SQL SERVER - How To find Nth Highest Salary of Employee

A Quite common Question in any of the forum - “How To find Nth Highest Salary of Employee”

For this, Let us first create a table Employee

Create Table Employee
(Empid int NOT NULL identity(1,1) Primary key,
Empname varchar(50),
DeptId int,
Salary money)

Insert into Employee
Select 'AAA',1,30000
Union ALL
Select 'BBB',1,45000
Union ALL
Select 'CCC',1,23000
Union ALL
Select 'DDD',1,30000
Union ALL
Select 'EEE',1,20000
Union ALL
Select 'FFF',1,70000
Union ALL
Select 'GGG',1,39000
Union ALL
Select 'HHH',1,130000
Union ALL
Select 'III',1,35000
Union ALL
Select 'JJJ',1,43000                                                        Union ALL
Select 'KKK',1,23000
Union ALL
Select 'LLL',1,80000
Union ALL
Select 'MMM',1,35000
Union ALL
Select 'NNN',1,65000

Now we will retrieve the details from the Employee table in the increasing order of Salary to easily cross check the results of the coming queries.

Select * From Employee order by Salary 

The Output is :

Now, If we want to get the first highest salary from the Employee table, we can easily write the query as below

Select Max(Salary) Max_salary From Employee

Well, Now we want to get the second highest salary  from the Employee table, this can also be achieved with little bit effort.

Select Max(Salary) as Second_max From Employee  A Where  Salary < (Select Max(Salary) From Employee)

Or

Select Top 1 Salary as Second_max From (
Select Distinct Top 2 Salary
From Employee Order by Salary Desc) Highest_Salaries
Order by Salary

Or

Select Distinct Salary as Second_max From Employee A Where 
2 =(Select Count(Distinct Salary) From Employee B Where
A.Salary <=B.Salary)

The out put is :

Similarly to get the 3rd, 4th,5th,6th,….nth Highest salary from the Employee table, we can replace 2 in the above query with 3,4,5…

The general query will be like this.

Select Top 1 Salary as Second_max From (
Select Distinct Top N Salary
From Employee Order by Salary Desc) Highest_Salaries
Order by Salary

Or

Select Distinct Salary as Second_max From Employee A Where  
N =(Select Count(Distinct Salary) From Employee B Where
A.Salary <=B.Salary)

where N=1,2,3,…..