Query to Find Nth Highest Salary of Employee In Sql Server | Query to Find 2nd or 3rd highest salary of employee in SQL Server

Introduction:

In this article I will explain how to find nth highest salary of the employee in SQL Server.

Description:

In many sites we will find one frequent question that is like how to find 2^nd highest salary of employee or how to find 3^rd highest salary of employee or how to find nth highest salary of employee in SQL Server. To get required highest salary we are having different alternative ways.

Before write queries to get 2^nd, 3^rd or nth highest salary of employee first design table in database and give name as “EmployeeDetails”

EmpID	EmpName	Salary
1	Suresh	7000
2	Prasanthi	8000
3	Mahesh	9000
4	Sai	10000
5	Nagaraju	11000
6	Mahendra	12000
7	Sanjay	13000
8	Santhosh	14000
9	Raju	15000
10	Phani	10000
11	Kumar	12000
12	Prasad	9000
13	Siva	12000
14	Madhav	14000
15	Donthi	11000

Once table design completed now we will see different ways to get 2^nd, 3^rd, etc or nth highest salary of employee.

Get Highest Salary from Employee Table

Use the below query to get 1^st, 2^nd, 3^rd, 4^th, 5^th ….etc highest salary of employee

SELECT MAX(Salary) as 'Salary' from EmployeeDetails where Salary NOT IN ( SELECT TOP n-1 (SALARY) from EmployeeDetails ORDER BY Salary Desc )

Here in above query we need to replace the “n” value with required highest salary of employee

To get 2^nd highest salary of employee then we need replace “n” with 2 our query like will be this

SELECT MAX(Salary) as 'Salary' from EmployeeDetails where Salary NOT IN ( SELECT TOP 1 (SALARY) from EmployeeDetails ORDER BY Salary Desc )

Output will be like this

Salary

14000

To get 3^rd highest salary of employee then we need replace “n” with 3 our query like will be this

SELECT MAX(Salary) as 'Salary' from EmployeeDetails where Salary NOT IN ( SELECT TOP 2 (SALARY) from EmployeeDetails ORDER BY Salary Desc )

Output will be like this

Salary

13000

The above query will help us to get highest salary from EmployeeDetails table suppose if we want to get Employee Details with particular highest salary then we need to use below query to get required details.

Get Employee Details with Particular Highest salary

Use the below query to get Employee Details with 1^st, 2^nd, 3^rd, 4^th, 5^th ….etc highest salary from table

SELECT * FROM EmployeeDetails e1 WHERE (n-1) = ( SELECT COUNT(DISTINCT(e2.Salary)) FROM EmployeeDetails e2 WHERE e2.Salary > e1.Salary)

Here in above query we need to replace the “n” value with required highest salary of table

To get 2^nd highest salary of Employee Details then we need replace “n” with 2 our query like will be this

SELECT * FROM EmployeeDetails e1 WHERE (1) = ( SELECT COUNT(DISTINCT(e2.Salary)) FROM EmployeeDetails e2 WHERE e2.Salary > e1.Salary)

Output will be like this

EmpID	EmpName	Salary
8	Santhosh	14000
14	Madhav	14000

To get 3^rd highest salary of employee then we need replace “n” with 3 our query like will be this

SELECT * FROM EmployeeDetails e1 WHERE (2) = ( SELECT COUNT(DISTINCT(e2.Salary)) FROM EmployeeDetails e2 WHERE e2.Salary > e1.Salary)

Output will be like this

EmpID	EmpName	Salary
7	Sanjay	13000

In this way we can get required employee details with particular highest salary.

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email