How to find the Nth highest salary in SQL Server?

This is one of the most common interview question for freshers and experienced candidates. We can do this in many ways.

Look at the following table?

 EMPLOYEE TABLE

Name	Salary
Sai	1000
sandeep	2000
kalyan	3000
vijendhar	4000

If you want to find the max salary from the table then we have to use max() function as follows.

select max(salary) from employee

Then result is 4000.

If you want to find 2nd highest salary.

select max(salary) from employee < (select max(salary) from employee)

                                         OR

select max(salary) from employee <> (select max(salary) from employee)

                                         OR

select max(salary) from employee not in (select max(salary) from employee)

Then result is 3000.

The following is the query to find nth highest salary.

select top 1 salary from

(select top N salary from employee 

order by salary desc)

result order by salary

Based on above query if you want to find 3rd highest salary, then write 3 at the place N as follows.

select top 1 salary from

(select top 3 salary from emp11 

order by salary desc)

result order by salary

Then the result is 2000.

                          Find Nth highest salary in SQL Server by using CTE?