Find the 2nd or 3rd or Nth highest salary

Tutorialsrack 20/02/2019 SQL

SQL Query to find 2nd or 3rd or Nth highest salary. This is a very common SQL Server Interview Question. There are several ways of finding the 2nd or 3rd or Nth highest salary.

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

Find 2nd, 3rd or Nth Highest salary using LINQ

Use the following script to create a table

Create table Employees
(
     ID int primary key identity,
     FirstName nvarchar(50),
     LastName nvarchar(50),
     Gender nvarchar(50),
     Salary int
)
GO

Insert into Employees values ('Sourabh', 'chauhan', 'Male', 70000)
Insert into Employees values ('Hemant', 'chauhan', 'Male', 60000)
Insert into Employees values ('Ravi', 'Thakur', 'Male', 45000)
Insert into Employees values ('Sourabh', 'Chauhan', 'Male', 70000)
Insert into Employees values ('Praveen', 'Yadav', 'Male', 45000)
Insert into Employees values ('Sanaya', 'sharma', 'Female', 30000)
Insert into Employees values ('Geetanjali', 'Mishra', 'Female', 35000)
Insert into Employees values ('Tom', 'Stanmore', 'Male', 80000)
GO

Table: Employees

ID	FirstName	LastName	Gender	Salary
1	Sourabh	Chauhan	Male	70000
2	Hemant	Chauhan	Male	60000
3	Ravi	Thakur	Male	45000
4	Sourabh	chauhan	Male	70000
5	Praveen	Yadav	Male	45000
6	Sanaya	Sharma	Female	30000
7	Geetanjali	Mishra	Female	35000
8	Tom	Stanmore	Male	80000

Find Nth highest salary using Sub Query

1st Method for finding Nth Highest Salary

select top 1 salary from
(select distinct top N Salary from Employees order by Salary desc)
as result order by salary
--Relpace N with Any Number, you want to find the highest salary

2nd Method:-

2nd Method for finding Nth highest salary

select min(salary) from(
select distinct top N salary from employees order by salary desc) as sal
--Relpace N with Any Number, you want to find the highest salary

Find Nth highest salary using CTE and Dense_Rank()

Nth highest salary using CTE and DENSE_RANK()

with cte as
(
select salary,DENSE_RANK() over (order by salary desc) as DenseRank from Employees
)
select top 1 salary from cte where DenseRank=N

--Relpace N with Any Number, you want to find the highest salary

For finding the Nth highest salary, we can use the above two queries

Find Nth highest salary using CTE and Row_Number()

Note:- The below query will only work if there are no duplicates. On Many websites, you have seen that the below query is used to find Nth highest salary. But it only works there are no duplicates

Nth Highest salary using ROW_NUMBER()

with CTE1 as(
select distinct salary,ROW_NUMBER() over (order by salary desc) as RowNumber
from Employees order by salary desc
)
select salary from CTE1 where RowNumber = N

--Relpace N with Any Number, you want to find the highest salary

Note:- Replace N with any Number, You want to find the highest salary. For example , if you want to find 3rd highest salary, then replace N with 3.

Tagged: nth highest salary 2nd or 3rd highest salary