Finding Nth Highest Salary of an Employee

Finding Nth Highest Salary of an Employee

-- FIND NTH HIGHEST SALARY OF AN EMPLOYEE

1)  Using Corelated Subquery
select * from Emp e1 where 1= (select count(distinct(e2.sal)) from Emp e2 where e2.sal>e1.sal);

2)  Using Rownum
select * from (select e.*, row_number() over(order by sal desc) rownumb from Emp e where sal is not null) where rownumb=3;

3) Using Rank
select * from (select e.*, rank() over(order by sal desc) ranking from Emp e where sal is not null) where ranking =2;