how to find nth highest salary in sql

finding nth highest salary in a table is the most common question asked in interviews. here is a way to do this task using dense_rank() function.

(i)
with res as(
select salary,dense_rank() over (order by salary desc) as topsal from emp
)
select salary from res where topsal='2'

or alternative:

(ii)
select top 1 salary from (select top 2 salary from emp order by salary desc) emp order by salary asc

or alternative:

(iii)
select e1.salary from emp as e1 where 2 = (select count(*) from emp as e2 where e1.salary<= e2.salary)

note: here all query find second highest , you can find nth highest salary using this query.