How to find nth Highest Salary in Oracle

We have taken 3rd (nth )  highest salary for example.


Type:1

SELECT empno, sal
FROM
(
select empno, sal, ROW_NUMBER() OVER (order by sal desc) RN 
from emp 
order by sal desc
)
WHERE RN = 3;


Type 2 : 

select sal from
(select emp.*,
       dense_rank() over (order by sal desc) rank
from emp)
where rank=3;



Type 3 :

                                                                               /* 3 for 3rd highest and 4 for 4th  highest  salary etc..*/


select distinct (a.sal)                                              
from emp a
WHERE 3= (SELECT count(distinct(b.sal)) from emp b
where a.sal<=b.sal);




2nd highest salary in SQL :


SELECT MAX(Sal) FROM Emp
WHERE Sal NOT IN (SELECT MAX(Sal) FROM Emp);


/* Display top three earner name and salaries from EMPLOYEES table */


SELECT ROWNUM as RANK, last_name ,salary
from (SELECT last_name , salary FROM employees
      ORDER BY salary desc) 
      
WHERE ROWNUM<=3;


Oracle assigns values to ROWNUM sequentially as rows are produced by the query - thus, the first row fetched gets ROWNUM=1, the second row fetched gets ROWNUM=2, the third row fetched gets ROWNUM=3, etc. Notice - for a row to be assigned ROWNUM=3 two preceding rows MUST be fetched. And this is why your query returns no rows. You're asking the database for the third row fetched - but rows 1 and 2 have never been fetched.