Find Nth Highest Salary In SQL Server

In this article, we will learn various ways to find highest salary from employee table. I have seen many interviewers ask this question frequently in different ways like find 2nd highest, 3rd highest, department wise highest salary, and many more.

Here we are going to learn step by step from creating table and insert data into tables using insert script. I will create department master table as well because I am planning to write queries to find department wise highest salary for each department. 

Create Employee table

CREATE TABLE [dbo].[Employees](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NOT NULL,
	[Email] [varchar](50) NOT NULL,
	[City] [varchar](50) NOT NULL,
	[Salary] [int] NOT NULL,
	[DepartmentId] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Create Department table

CREATE TABLE [dbo].[Department](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[DepartmentName] [varchar](100) NULL
) ON [PRIMARY]

Create insert script to insert data in both tables.

insert into Department (DepartmentName) values('Marketing')
Go
insert into Department (DepartmentName) values('Operations')
Go
insert into Department (DepartmentName) values('Finance')
Go
insert into Department (DepartmentName) values('HR')
Go
insert into Department (DepartmentName) values('Information Technology')
Go
insert into Department (DepartmentName) values('Sales')
Go
insert into Department (DepartmentName) values('Purchase')

SET IDENTITY_INSERT [dbo].[Employees] ON 

INSERT [dbo].[Employees] ([Id], [FirstName], [LastName], [Email], [City], [Salary], [DepartmentId]) VALUES (3, N'Jinesh', N'Patel', N'[email protected]', N'Banglore', 20000, 1)
INSERT [dbo].[Employees] ([Id], [FirstName], [LastName], [Email], [City], [Salary], [DepartmentId]) VALUES (4, N'Poojan', N'Pandya', N'[email protected]', N'Rajkot', 10000, 1)
INSERT [dbo].[Employees] ([Id], [FirstName], [LastName], [Email], [City], [Salary], [DepartmentId]) VALUES (5, N'Priyank', N'Patel', N'[email protected]', N'Baroda', 15000, 1)
INSERT [dbo].[Employees] ([Id], [FirstName], [LastName], [Email], [City], [Salary], [DepartmentId]) VALUES (6, N'Kritika', N'Srivastav', N'[email protected]', N'Pune', 15000, 1)
INSERT [dbo].[Employees] ([Id], [FirstName], [LastName], [Email], [City], [Salary], [DepartmentId]) VALUES (7, N'Pritesh', N'Patel', N'[email protected]', N'Banglore', 20000, 1)
INSERT [dbo].[Employees] ([Id], [FirstName], [LastName], [Email], [City], [Salary], [DepartmentId]) VALUES (8, N'Mayank', N'Srivastav', N'[email protected]', N'Jaipur', 20000, 2)
INSERT [dbo].[Employees] ([Id], [FirstName], [LastName], [Email], [City], [Salary], [DepartmentId]) VALUES (9, N'Jayesh', N'Pandey', N'[email protected]', N'Jaipur', 25000, 2)
INSERT [dbo].[Employees] ([Id], [FirstName], [LastName], [Email], [City], [Salary], [DepartmentId]) VALUES (10, N'Brinda', N'pradhan', N'[email protected]', N'Jaipur', 10000, 2)
INSERT [dbo].[Employees] ([Id], [FirstName], [LastName], [Email], [City], [Salary], [DepartmentId]) VALUES (11, N'Anjum', N'dave', N'[email protected]', N'Jaipur', 12000, 2)
INSERT [dbo].[Employees] ([Id], [FirstName], [LastName], [Email], [City], [Salary], [DepartmentId]) VALUES (12, N'Priyank', N'Srivastav', N'[email protected]', N'Jaipur', 22000, 3)
INSERT [dbo].[Employees] ([Id], [FirstName], [LastName], [Email], [City], [Salary], [DepartmentId]) VALUES (13, N'Dharmin', N'Pandey', N'[email protected]', N'Jaipur', 35000, 3)
INSERT [dbo].[Employees] ([Id], [FirstName], [LastName], [Email], [City], [Salary], [DepartmentId]) VALUES (14, N'Khusbu', N'pradhan', N'[email protected]', N'Jaipur', 13000, 3)
INSERT [dbo].[Employees] ([Id], [FirstName], [LastName], [Email], [City], [Salary], [DepartmentId]) VALUES (15, N'priyam', N'dave', N'[email protected]', N'Jaipur', 22000, 3)
INSERT [dbo].[Employees] ([Id], [FirstName], [LastName], [Email], [City], [Salary], [DepartmentId]) VALUES (16, N'Divya', N'Srivastav', N'[email protected]', N'Jaipur', 32000, 4)
INSERT [dbo].[Employees] ([Id], [FirstName], [LastName], [Email], [City], [Salary], [DepartmentId]) VALUES (17, N'kishan', N'Pandey', N'[email protected]', N'Jaipur', 15000, 4)
INSERT [dbo].[Employees] ([Id], [FirstName], [LastName], [Email], [City], [Salary], [DepartmentId]) VALUES (18, N'chintan', N'pradhan', N'[email protected]', N'Jaipur', 23000, 4)
INSERT [dbo].[Employees] ([Id], [FirstName], [LastName], [Email], [City], [Salary], [DepartmentId]) VALUES (19, N'Rohit', N'patel', N'[email protected]', N'Jaipur', 12000, 4)
INSERT [dbo].[Employees] ([Id], [FirstName], [LastName], [Email], [City], [Salary], [DepartmentId]) VALUES (20, N'Jignesh', N'patel', N'[email protected]', N'Jaipur', 65000, 5)
INSERT [dbo].[Employees] ([Id], [FirstName], [LastName], [Email], [City], [Salary], [DepartmentId]) VALUES (21, N'Darpan', N'Pandey', N'[email protected]', N'Jaipur', 54000, 5)
INSERT [dbo].[Employees] ([Id], [FirstName], [LastName], [Email], [City], [Salary], [DepartmentId]) VALUES (22, N'Sagar', N'Panchal', N'[email protected]', N'Jaipur', 85000, 5)
INSERT [dbo].[Employees] ([Id], [FirstName], [LastName], [Email], [City], [Salary], [DepartmentId]) VALUES (23, N'Vivek', N'patel', N'[email protected]', N'Jaipur', 55000, 5)
INSERT [dbo].[Employees] ([Id], [FirstName], [LastName], [Email], [City], [Salary], [DepartmentId]) VALUES (24, N'Naimish', N'Patel', N'[email protected]', N'Jaipur', 63000, 5)
INSERT [dbo].[Employees] ([Id], [FirstName], [LastName], [Email], [City], [Salary], [DepartmentId]) VALUES (25, N'Nainesh', N'Gajjar', N'[email protected]', N'Jaipur', 22000, 5)
SET IDENTITY_INSERT [dbo].[Employees] OFF

We are ready with our both tables. These two tables we are going to use to write query. Now I will demonstrate how we are going to write SQL queries to get Nth highest salary.

Finding nth Highest Salary In SQL Server

Lets start step by step with different ways. First I will get all employees records from employee table so we will be clear with all employees data.

select * from Employees

Find max salary using the max function,

select max(Salary) as Salary from Employees;

Using top key word and subquery

declare @nthHighest int 
set @nthHighest = 5 -- This one is parameter for which highest position you want to find Here 5th Highest
select top 1 Salary from
(
select distinct top (@nthHighest) salary as Salary from Employees order by Salary desc
) as result
order by Salary;

You can find nth position highest salary using above query. You just need to change declared variable (set @nthHighest = 5) according to you requirement. In the above query, I have set it to 5 so it will return 5th highest salary as per below screen where I have marked with red color.

Find 2nd highest salary using sub-query

select top 2 Salary from Employees order by Salary desc

-----------------------------------------------------

Select Max(Salary) as Salary from Employees 
where Salary <(select MAX(Salary) from Employees)

 

Using common table expression

Declare @Nhighest int
set @Nhighest =3;

WITH RESULT AS  
(  
    SELECT distinct SALARY,  
           DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK  
    FROM Employees  
)  
SELECT TOP 1 SALARY  
FROM RESULT  
WHERE DENSERANK = @Nhighest 

Using common table expression we can find nth highest salary as above query. I have set @Nhighest =3 so it will return 3rd highest salary from employees table.

Declare @Nhighest int
set @Nhighest = 5;

WITH RESULT AS  
(  
    SELECT distinct SALARY,  
           DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK  
    FROM Employees  
)  
SELECT TOP 1 SALARY  
FROM RESULT  
WHERE DENSERANK = @Nhighest 

Above query I have change @Nhighest  variable values to 5 so it will return 5th highest salary from employee. This way you just need to change this variable only to get nth highest salary.

Find Nth highest salary department wise using common table expression.

Declare @Nhighest int
set @Nhighest = 2;

WITH RESULT AS (
SELECT *,
       DENSE_RANK() OVER(PARTITION BY DepartmentId ORDER BY Salary DESC) AS DENSERANK
    FROM Employees
 )
 SELECT distinct salary, d.DepartmentName
 FROM RESULT R
 join Department d on d.Id= DepartmentId 
 WHERE DENSERANK = @Nhighest ;

Using common table expression with dense_rank() and partition we can find department wise highest salary, here is highest salary of each department using above query. Here I have set @Nhighest value to 2 so it will return 2nd highest salary of each department.

In this article I have demonstrated how to find nth highest salary using different way. We have used max function, top keyword, sub-query and common table expression to find nth highest salary. Hope this article will help you find nth highest salary using the above methods. I have attached a single script for all queries with table creation and data insertion script. Please find other useful articles on SQL server topics that may help you to understand SQL concepts.

• Import Data From Excel Data Into SQL Table In Microsoft SQL Server 2016
• Improve Store Procedure Performance In SQL Server/ Store Procedure Performance Tuning