One of the common SQL question is how to find Nth highest salary in SQL Server or how to find 3rd highest salary from a employee table in SQL Server, so in this article, I have mentioned how we can find nth highest salary in SQL Server.

Suppose, we have below given SQL Server table

find-nth-third-highest-salary-sql-server

You can also create sample database in your local SQL using below SQL Script

CREATE TABLE [dbo].[EmployeeSalary](
	[Id] [int] NOT NULL,
	[Salary] [float] NULL,
	[Name] [varchar](100) NULL,
 CONSTRAINT [PK_EmployeeSalary] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[EmployeeSalary] ([Id], [Salary], [Name]) VALUES (1, 100.1, N'Vikram')
INSERT [dbo].[EmployeeSalary] ([Id], [Salary], [Name]) VALUES (2, 700, N'Hiren')
INSERT [dbo].[EmployeeSalary] ([Id], [Salary], [Name]) VALUES (3, 300, N'Hari')
INSERT [dbo].[EmployeeSalary] ([Id], [Salary], [Name]) VALUES (4, 700, N'Hiren')
INSERT [dbo].[EmployeeSalary] ([Id], [Salary], [Name]) VALUES (5, 600, N'Hari')
INSERT [dbo].[EmployeeSalary] ([Id], [Salary], [Name]) VALUES (6, 500, N'Suresh')
INSERT [dbo].[EmployeeSalary] ([Id], [Salary], [Name]) VALUES (7, 600, N'Hari')
GO

Now let's take a look at how to find Nth Highest salary in SQL Server

Get 2nd Highest Salary in SQL Server

We will start with the simple query to get 2nd highest salary in SQL Server

Where we have 2 queries, query inside query

select (SELECT MAX(Salary) FROM EmployeeSalary
WHERE Salary
NOT IN 
(SELECT MAX(Salary) FROM EmployeeSalary )) as [2nd_max_salary]

Output, considering above table

2nd_max_salary
--------------
600

In the above query, inner query is selecting maximum employee salary then outer query select salary highest other than inner query result, making it as second highest salary.

Get 3rd Highest Salary in SQL Server using Dense_Rank()

One of the easiest way to get 3rd Highest salary in SQL Server is using Dense_Rank() and CTE

WITH RESULT AS
(
    SELECT Salary,
           DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK
    FROM EmployeeSalary
)
SELECT TOP 1 Salary
FROM RESULT
WHERE DENSERANK = 3
GO

Once you will execute above code, output would be as below

third-highest-salary-in-sql-server

3rd Highest or Nth highest Salary using Distinct

If the above Dense_Rank() based solution looks difficult, we can also use Distinct keyword to get nth highest salary, syntax of query will be as below

SELECT salary 
FROM EmployeeSalary e1
WHERE N-1 = (SELECT COUNT(DISTINCT salary) FROM EmployeeSalary e2
WHERE e2.salary > e1.salary)

Where N is the number for which you need record.

For example, 3rd highest salary query would be as below

SELECT salary 
FROM EmployeeSalary e1
WHERE 3-1 = (SELECT COUNT(DISTINCT salary) FROM EmployeeSalary e2
WHERE e2.salary > e1.salary)

Output:

salary
------
500

As 2nd highest salary can have multiple records, so considering above syntax query would be as below

SELECT Name,salary 
FROM EmployeeSalary e1
WHERE 2-1 = (SELECT COUNT(DISTINCT salary) FROM EmployeeSalary e2
WHERE e2.salary > e1.salary)

Output:

Name	salary
------------------
Hari	600
Hari	600

The distinct keyword is there to deal with duplicate salaries in the table.

But this approach is slower than above Dense_Rank one as the inner query will run for every row processed by the outer query.

You may also like to read:

How to Select LAST N Row in SQL Server

GO Statement in sql server

Extract String after a character or before character in SQL Server

Convert Int To String (varchar) in SQL Server

If-Else in SQL Server with Example

Find Duplicate Rows in SQL Server

Change theme in SQL Server Management Studio (SSMS, Dark Mode)