How to find nth highest salary in sql server

There are several ways of finding the nth highest salary.   

1    1. How to find nth highest salary in SQL Server using a Sub-Query
2. How to find nth highest salary in SQL Server using a CTE
3. How to find the 2nd, 3rd or 4th or 5th..... highest salary

Let's use the following Employee1 table for this demo. 

Use the following code to create Employee1 table

CREATE TABLE Employee1(

EmpID int primary key IDENTITY(1,1) ,

EmpName varchar(50) ,

Salary int ,

   [Address] varchar(200) 

   )

After creating the table execute below code for inserting the data into database table.

Insert into Employee1 values('Dev','100','Delhi'),

('Piyush','30','Madhya Pradesh'),

('Raju','600','Bangalore'),

('Narendra','12000','Uttar Pradesh'),

('Pankaj','1800','Sikkim'),

('Ravi','2800','Gujrat'),

('Mahantesh','10','Bangalore'),

('Ram','4000','Delhi'),

('Ramesh','2000','Nagpur')

To find the highest salary. We can simply use the Max() function as shown below.

Select  Max(Salary)as [Highest salary]  from Employee1

How to get the second highest salary use a sub query along with Max() function as shown below.


Select Max(Salary)[Highest Salary] 
from Employee1 
where 

Salary < (Select Max(Salary) from Employee1)







To find nth highest salary using Sub-Query

SELECT TOP 1 SALARY

FROM (

      SELECT DISTINCT TOP N SALARY

      FROM EMPLOYEES

      ORDER BY SALARY desc

      ) RESULT

ORDER BY SALARY asc

For Example:

SELECT TOP 1 SALARY

FROM (

      SELECT DISTINCT TOP 5 SALARY

      FROM EMPLOYEE1

      ORDER BY SALARY desc

      ) RESULT

ORDER BY SALARY asc

To find nth highest salary using CTE

WITH RESULT AS

(

    SELECT SALARY,

           DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK

    FROM EMPLOYEES

)

SELECT TOP 1 SALARY

FROM RESULT

WHERE DENSERANK = N

To find 4th highest salary we can use any of the above queries. Simple replace N with 4. 
Similarly, to find 3rd highest salary, simple replace N with 3. 
For Example

WITH RESULT AS
(
    SELECT SALARY,
           DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK
    FROM EMPLOYEE1
)
SELECT TOP 1 SALARY
FROM RESULT

WHERE DENSERANK = 4

Some other way to get nth highest salary.

Select * from Employee1 emp1
where (4-1)=
(select  count(distinct emp2.salary)
from Employee1 emp2
where emp2.salary>emp1.salary

)

Thus we can get or find nth highest salary for reference you can check with all data with above define table Employee1.