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

SQL

Copy

Create Department table

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

SQL

Copy

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')

SQL

Copy

SET IDENTITY_INSERT [dbo].[Employees] ON

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

SQL

Copy

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

SQL

Copy

Find max salary using the max function,

select max(Salary) as Salary from Employees;

SQL

Copy

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;

SQL

Copy

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)

SQL

Copy

 

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

SQL

Copy

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

SQL

Copy

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 ;

SQL

Copy

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