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."
Previous
Next Post »

1 comments:

Click here for comments
Anonymous
admin
September 23, 2014 ×

Umm, what about using ranking function?

Congrats bro Anonymous you got PERTAMAX...! hehehehe...
Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..