Sql server query to get second,third,fourth or nth highest/maximum salary of employee

Introduction: In this article i am going to explain multiple ways to retrieve/get/find 2nd, 3rd, 4th, 5th,6th.....nth highest/maximum salary from employee table or record in sql server database table.

Description: It is one of the mostly asked sql interview question from the fresher and intermediate level of developers. So i have decided to provide some easiest way to get 2nd, 3rd, 4th.....nth highest/maximum salary from employee table in sql server.

In previous articles i explained How to  Use CTE to remove duplicate records from Sql Server database table and Create Sql server database script and Create database from that script and What is the use of SELF JOIN in SQL SERVER with example and Example to explain Transaction in Sql server using Asp.net and What is cursor? Advantages and disadvantages,when to use and example to explain and Get column values as comma separated list in sql server | Convert column values to row 

Implementation:  Let's create a sample table "Tb_EmpDetails" in sql server with the columns and data type as shown below

Column name	Data Type
EmpId	Int(Primary key)
EmpName	varchar(100)
Age	Int
Salary	decimal(18, 2)

or  you can simple create above table using the script below or

CREATE TABLE [dbo].[Tb_EmpDetails](

                [EmpId] [int] IDENTITY(1,1) NOT NULL,

                [EmpName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

                [Age] [int] NULL,

                [Salary] [decimal](18, 2) NULL,

 CONSTRAINT [PK_Tb_Emp] PRIMARY KEY CLUSTERED

(

                [EmpId] ASC

)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

Now insert some sample data into Tb_EmpDetails

INSERT INTO Tb_EmpDetails VALUES('Raman',22,18000.00)

INSERT INTO Tb_EmpDetails VALUES('Rishabh',23,22000.00)

INSERT INTO Tb_EmpDetails VALUES('Anoop',21,20000.00)

INSERT INTO Tb_EmpDetails VALUES('Imran',25,16000.00)

INSERT INTO Tb_EmpDetails VALUES('Aamir',20,25000.00)

INSERT INTO Tb_EmpDetails VALUES('Abhinav',22,30000.00)

INSERT INTO Tb_EmpDetails VALUES('Saurav',24,15000.00)

INSERT INTO Tb_EmpDetails VALUES('Anuj',23,25000.00)

INSERT INTO Tb_EmpDetails VALUES('Sanjay',22,12000.00)

INSERT INTO Tb_EmpDetails VALUES('Aman',22,18000.00)

INSERT INTO Tb_EmpDetails VALUES('Prince',25,24000.00)

Now check the data in table using the command

SELECT * FROM Tb_EmpDetails

Output will be as:

EmpId	EmpName	Age	Salary
1	Raman	22	18000.00
2	Rishabh	23	22000.00
3	Anoop	21	20000.00
4	Imran	25	16000.00
5	Aamir	20	25000.00
6	Abhinav	22	30000.00
7	Saurav	24	15000.00
8	Anuj	23	25000.00
9	Sanjay	22	12000.00
10	Aman	22	18000.00
11	Prince	25	24000.00

Now we can query the above table to get the required highest salary

Below are some of the ways to get the required highest salary

Method 1:

Let's write the query to get 2nd highest salary from table

SELECT TOP 1 Salary FROM

(

      SELECT DISTINCT TOP 2 Salary FROM Tb_EmpDetails ORDER BY Salary DESC

) AS T ORDER BY Salary ASC

Result of the above query will be:

Salary

25000.00

Now let's write the query to get 3rd highest salary from table

SELECT TOP 1 Salary FROM

(

      SELECT DISTINCT TOP 3 Salary FROM Tb_EmpDetails ORDER BY Salary DESC

     ) AS T ORDER BY Salary ASC

Result of the above query will be:

Salary

24000.00

Similarly you can get 4th, 5th, 6th...nth salary using the following query structure

SELECT TOP 1 Salary FROM

(

      SELECT DISTINCT TOP N Salary FROM Tb_EmpDetails ORDER BY Salary DESC

) AS T ORDER BY Salary ASC

Note: You just need to replace N with the required highest salary of employee. E.g if you want to get 5th highest salary from the table then the above query will be simply

SELECT TOP 1 Salary FROM

(

      SELECT DISTINCT TOP 5 Salary FROM Tb_EmpDetails ORDER BY Salary DESC

) AS T ORDER BY Salary ASC

Result of the above query will be: 

Salary

20000.00

Method 2:

Let's write the query to get 2nd highest salary from table

SELECT MAX(Salary) AS 'Salary' FROM Tb_EmpDetails

WHERE Salary NOT IN

(

 SELECT DISTINCT  TOP 1 (SALARY) FROM Tb_EmpDetails ORDER BY Salary DESC

)

Result of the above query will be:

Salary

25000.00

Let's write the query to get 3rd highest salary from table

SELECT MAX(Salary) AS 'Salary' FROM Tb_EmpDetails

WHERE Salary NOT IN

(

 SELECT DISTINCT  TOP 2 (SALARY) FROM Tb_EmpDetails ORDER BY Salary DESC

)

Result of the above query will be:

Salary

24000.00

Similarly you can get 4th, 5th, 6th...nth salary using the following query structure

SELECT MAX(Salary) AS 'Salary' FROM Tb_EmpDetails

WHERE Salary NOT IN

(

 SELECT DISTINCT TOP N-1(SALARY) FROM Tb_EmpDetails ORDER BY Salary DESC

)

Note: You just need to replace N with the required highest salary of employee. E.g if you want to get 5th highest salary from the table then the above query will be simply

SELECT MAX(Salary) AS 'Salary' FROM Tb_EmpDetails

WHERE Salary NOT IN

(

 SELECT DISTINCT TOP 4 (SALARY) FROM Tb_EmpDetails ORDER BY Salary DESC

)

Result of the above query will be:

Salary

20000.00

Method 3:

Let's write the query to get 2nd highest salary from table

SELECT MIN(Salary) AS 'Salary' FROM Tb_EmpDetails

WHERE Salary IN

(

 SELECT DISTINCT  TOP 2 Salary FROM Tb_EmpDetails ORDER BY Salary DESC

)

Result of the above query will be:

Salary

25000.00

Similarly you can get 4th, 5th, 6th...nth salary using the following query structure

SELECT MIN(Salary) AS 'Salary' FROM Tb_EmpDetails

WHERE Salary IN

(

 SELECT DISTINCT  TOP N Salary FROM Tb_EmpDetails ORDER BY Salary DESC

)

Note: You just need to replace N with the required highest salary of employee. E.g if you want 5th highest salary then the above query will be simply

SELECT MIN(Salary) AS 'Salary' FROM Tb_EmpDetails

WHERE Salary IN

(

 SELECT DISTINCT  TOP 5 Salary FROM Tb_EmpDetails ORDER BY Salary DESC

)

Result of the above query will be:

Salary

20000.00

If you just want to get the the second highest salary then following queries can also give the results

Method 4:

Let's write the query to get 2nd highest salary from table

SELECT MAX(salary)AS 'Salary' FROM Tb_EmpDetails WHERE salary NOT IN (SELECT MAX(salary) FROM Tb_EmpDetails)

Result of the above query will be:

Salary

25000.00

Method 5:

Let's write the query to get 2nd highest salary from table

SELECT MAX(salary) AS 'Salary' FROM Tb_EmpDetails WHERE salary < (SELECT MAX(salary) FROM Tb_EmpDetails)

Result of the above query will be:

Salary

25000.00

Now over to you:

" I hope you have learned how to get second,third,fourth or nth highest/maximum salary of employee or record with this example and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."

• Tweet
• Share
• Share
• Share

Related Post