Find Nth highest salary in SQL Server by using CTE?

We can find the nth salary by using CTE also as follows.

 EMPLOYEE TABLE

Name	Salary
Sai	1000
sandeep	2000
kalyan	3000
vijendhar	4000

Here we are used DENSE_RANK()

select salary, DENSE_RANK() over (order by salary desc) as densrank

from employee

Salary	densrank
4000	1
3000	2
2000	3
1000	4

The following is the query by using CTE

WITH Result

AS

(

select salary, DENSE_RANK() over (order by salary desc) as densrank

from employee

)

select top 1 salary 

from result where result.densrank=N

If you want to find second highest salary, then following is a query where we need to replace 2 instead of N.

WITH Result

AS

(

select salary, DENSE_RANK() over (order by salary desc) as densrank

from employee

)

select top 1 salary 

from result where result.densrank=2

Now the result is 3000.

Some of the blogs will use ROW_NUMBER(), but it works only if you do not have duplicates otherwise result might be wrong. The following is the query.

with result as

(

select salary, ROW_NUMBER() over (order by salary desc) as rownumber

from employee

)

select salary from result where rownumber=N

                                 How to find the Nth highest salary in SQL Server?