SQL SERVER – Find Nth Highest Salary of Employee –Query to Find 2nd or 3rd highest salary of employee in SQL Server

In this article I am going to explain how you can find nth highest salary of the employee in SQL Server.

It is very common question asked by interviewer that how to find 2nd highest salary of employee or how to find 3rd highest salary of employee or how to find nth highest salary of employee in SQL Server. Here in this Sql Server Query Example i will Show you how to find 2nd,3rd,4th,.......,nth highest salary of employee. 

 For this sql server query example i have created a Temporary Sql server Table in Sql Server Database named #EmpDetails .And enterd some values to this sql server table.The script is given below. 

CREATE TABLE #EmpDetails(EMPID
int,EMPNAME NVARCHAR(10),EMPSAL int)
INSERT INTO #EmpDetails VALUES
(1,'RAJ',2000)
INSERT INTO #EmpDetails VALUES(2,'SURESH',12000)
INSERT INTO #EmpDetails VALUES(3,'SURAJ',15000)
INSERT INTO #EmpDetails VALUES(4,'SUNEEL',14000)
INSERT INTO #EmpDetails VALUES(5,'SANJEEV',13000)
INSERT INTO #EmpDetails VALUES(6,'ATUL',12070)
INSERT INTO #EmpDetails VALUES(7,'VINEET',12600)
INSERT INTO #EmpDetails VALUES(8,'VIVEK',12700)

Now Check the table for values.

 select * from #EmpDetails

  It will display The following Rows.


EMPIDEMPNAMEEMPSAL
1RAJ2000
2SURESH12000
3SURAJ15000
4SUNEEL14000
5SANJEEV13000
6ATUL12070
7VINEET12600
8VIVEK12700

Once table creation is completed now we will see different ways to get 2nd, 3rd, etc or nth highest salary of employee. The below sql query can be used to get 1st, 2nd, 3rd, 4th, 5th ….etc highest salary of employee.

SELECT TOP 1 EMPSAL
FROM (
SELECT DISTINCT TOP n EMPSAL
FROM
#EmpDetails
ORDER BY EMPSAL DESC) a
ORDER BY EMPSAL

Replace n with 2 to find the second (2nd) highest salary of employee query will look like this . 

SELECT TOP 1 EMPSAL
FROM (
SELECT DISTINCT TOP 2 EMPSAL
FROM
#EmpDetails
ORDER BY EMPSAL DESC) a
ORDER BY EMPSAL

Result will be
EMPSAL
----------
 14000

Replace n with 3 to find the third (3rd) highest salary of employee query will look like this . 


SELECT TOP 1 EMPSAL
FROM (
SELECT DISTINCT TOP 3 EMPSAL
FROM
#EmpDetails
ORDER BY EMPSAL DESC) a
ORDER BY EMPSAL

Result will be
EMPSAL
 -------------
13000  
Tags: , ,

Join Us!

Newer Post Older Post Home