SQL SERVER - Find Nth Highest Salary of Employee - Query to Retrieve the Nth Maximum value

April 2, 2008 by pinaldave

This question is quite a popular question and it is interesting that I have been receiving this question every other day. I have already answer this question here. “How to find Nth Highest Salary of Employee”.

Please read my article here to find Nth Highest Salary of Employee table : SQL SERVER - Query to Retrieve the Nth Maximum value

I have re-wrote the same article here with example of SQL Server 2005 Database AdventureWorks : SQL SERVER - 2005 - Find Nth Highest Record from Database Table

Just a day ago, I have received another script to get the same result from one of the blog reader Pravin Phatangare, let us see his simple method here.

For particular example of employee :

How to get 1st, 2nd, 3rd, 4th, nth topmost salary from an Employee table

The following solution is for getting 6th highest salary from Employee table ,

SELECT TOP 1 salary 
    FROM (
    SELECT DISTINCT TOP 6 salary 
        FROM employee
        ORDER BY salary DESC) a 
    ORDER BY salary

You can change and use it for getting nth highest salary from Employee table as follows

SELECT TOP 1 salary 
    FROM (
    SELECT DISTINCT TOP n salary 
        FROM employee
        ORDER BY salary DESC) a 
    ORDER BY salary

where n > 1 (n is always greater than one)

Same example converted in SQL Server 2005 to work with Database AdventureWorks.

USE AdventureWorks;
GO
SELECT TOP 1 Rate
    FROM (    
    SELECT DISTINCT TOP 4 Rate
        FROM HumanResources.EmployeePayHistory
        ORDER BY Rate DESC) A
    ORDER BY Rate
GO

Reference : Pinal Dave (http://www.SQLAuthority.com), Pravin Phatangare

Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 35 Comments

35 Responses to “SQL SERVER - Find Nth Highest Salary of Employee - Query to Retrieve the Nth Maximum value”

on April 7, 2008 at 12:20 pm Dharmendra k Dixit

Hi Pinal,

myself Dharmendra, and am a Regular reader of ‘SQlAuthority’.
its a Good one Article about finding nth Higest Salary..
if am not wrong we can also do the same thing by using aggreate function like Min or Max..

Like if i want to know 4th Highest Salary then i can

SELECT MIN(Sal) FROM TableName
WHERE Sal IN
(SELECT TOP 4 Sal FROM TableName ORDER BY Sal DESC)

Regards
Dharmendra k Dixit
on April 8, 2008 at 6:23 pm Water

Works like a Charm!!

I think this question is one of the highest hits in interviews…

Thanks.
on April 10, 2008 at 3:52 pm sisira

how can i get second hight salary or 3 rd hight salary
here
iam giving example
select top 1 quantity from(SELECT TOP n-1 quantity FROM (SELECT DISTINCT TOP n quantity FROM productorder ORDER BY quantity DESC) as A ORDER BY quantity desc )as B order by quantity

n->represt top nth records
n-1 is the which highest salary you want
on April 11, 2008 at 12:33 am Imran

@ Dharmendar

Bro.. your code is quite long and might be resource consuming because ,

1. your are selecting the minimum from a whole bunch of values.

2. You need to write Distinct in the query only then you will give the correct answer.

Like your code should be :

SELECT MIN (SALARY )
FROM UEXAMPLE1
WHERE SALARY IN (SELECT DISTINCT TOP 4 SALARY FROM UEXAMPLE1 ORDER BY SALARY DESC)
on April 12, 2008 at 1:42 pm TESFAY

HELLO !
THIS QUESTION IS DIFFICULT .
BUT I TRY THE ANSWER
SELECT TOP 5 * FROM EMPLOYEE ORDER BY EMP_SALARY DESC; ——–> (THIS IS USED FOR RETRIEVE THE MAXIMUM TOP 5 PAID EMPLOYEES )
OR
TO RETRIEVE THE MIN VALUE USE THIS
SELECT TOP 5 * FROM EMPLOYEE ORDER BY EMP_SALARY;

THANK YOU.
on April 20, 2008 at 3:53 pm FSL

Nice.

It really helps a lot.

Thanks.
on April 23, 2008 at 12:57 pm Dharmendra k Dixit

To Imran..

Yes Bro ..Thanks for ur Suggestion..

it might be resource Consuming..
but i think so, its an alternative for finding nth Highest Salary..

looking forward ur openion..

Regards
Dharmendra k Dixit
on May 19, 2008 at 9:44 am sstealth

hi there guys i need a query but i can´t get it
here i go the table has department_id, salary, employee_id

how can i increase the salary of all employees of a department in the min salary paid in that department

as example department 1 the min sal paid is 1000, i have to paid now to each employee his salary +1000
on May 19, 2008 at 11:15 pm Chirag

I think following query will give second highest salary. You can get nth highest by replacing 2 with n.

select max(sal)from emp e1 where 2<=(select count(*) from
emp e2 where e1.sal <= e2.sal);
on May 29, 2008 at 4:02 pm Praveen

Hi,
I am Praveen. I am visiting this site for first time. The above query is really good and generic. We can use this also to get nth lowest salary by just changing the order from DESC to ASC.
on June 16, 2008 at 12:30 pm sameer

hi
i hv tried with this query but i am not able to get the exat answer
i tried with this query it works fine
===========================
put any digit instead of 5 u can get exat result

select distinct a.salary from Salary a where 5=( select count(distinct b.salary) from Salary b where a.salary<=b.salary)
==================================
ok friends tell me which query wil be faster
this query or using top n query
u can mail me at
dolagobinda.sahoo@gmail.com for my feed back
on June 17, 2008 at 11:56 am Sudev Gandhi

For SQL Server 2005 you have a Windowed function ROW_NUMBER, which you can use as follows:

SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (order by EMP.SAL DESC) AS RANK FROM EMP) v where RANK = 10;
on June 17, 2008 at 12:00 pm Sudev Gandhi

In continuation to my above comment, I agree that this solution might not give exact result while SAL has duplicate values, but again one can play with the query a little bit. and get desired results.
on July 22, 2008 at 3:29 pm Paranthagan

I required sql query for select top 2 highest paid employee from each department
on July 30, 2008 at 2:38 pm Mookkandi

Hi friends,

select max(sal)from emp e1 where 2<=(select count(*) from
emp e2 where e1.sal <= e2.sal);

This query is ’selecting the n th highest salary’. This query is correct.
I want know how to run this query. please explain this sql query.
on August 13, 2008 at 5:34 pm m.k.c.prasad

hai ,

iam just asked that i want to know the maximum salary and minum salary along with the employee details
on August 16, 2008 at 4:23 am Vamshi

Hi Prasad,

Hope this will work for you

CREATE TABLE #T1 (ID1 INT, [Name] NVARCHAR(50), Salary INT)
INSERT INTO #T1 VALUES (1, ‘Vamshi’, 1000)
INSERT INTO #T1 VALUES (2, ‘xxxxx’, 2000)
INSERT INTO #T1 VALUES (3, ‘yyyyy’, 3000)
INSERT INTO #T1 VALUES (4, ‘zzzzz’, 4000)
INSERT INTO #T1 VALUES (5, ’sssss’, 5000)
INSERT INTO #T1 VALUES (6, ‘ccccc’, 6000)
INSERT INTO #T1 VALUES (7, ‘ppppp’, 2000)
INSERT INTO #T1 VALUES (8, ‘aaaaa’, 4000)
INSERT INTO #T1 VALUES (9, ‘bbbbb’, 5000)
INSERT INTO #T1 VALUES (10, ‘eeeee’, 5000)

SELECT a.ID1, a.[Name], a.Salary AS Salary
FROM #T1 AS a
WHERE a.Salary IN (
SELECT MIN(b.Salary)
FROM #T1 AS b)
UNION
SELECT a.ID1, a.[Name], a.Salary AS Salary
FROM #T1 AS a
WHERE a.Salary IN (
SELECT MAX(b.Salary)
FROM #T1 AS b)
ORDER BY a.Salary

DROP TABLE #T1
GO

The above query will also give you List of Employees who has Min Salary and List of employees having Max Highest Salary

Vamshi
on August 16, 2008 at 4:26 am Vamshi

For getting list of employees with Nth Highest Salary, Here is another approach, Probably this would be similar to one of the above queries

DECLARE @n INT — Variable for accepting N

SELECT @n = 2 — Get the Second Highest Salary

CREATE TABLE #T1 (ID1 INT, [Name] NVARCHAR(50), Salary INT)
INSERT INTO #T1 VALUES (1, ‘Vamshi’, 1000)
INSERT INTO #T1 VALUES (2, ‘xxxxx’, 2000)
INSERT INTO #T1 VALUES (3, ‘yyyyy’, 3000)
INSERT INTO #T1 VALUES (4, ‘zzzzz’, 4000)
INSERT INTO #T1 VALUES (5, ’sssss’, 5000)
INSERT INTO #T1 VALUES (6, ‘ccccc’, 6000)
INSERT INTO #T1 VALUES (7, ‘ppppp’, 2000)
INSERT INTO #T1 VALUES (8, ‘aaaaa’, 4000)
INSERT INTO #T1 VALUES (9, ‘bbbbb’, 5000)
INSERT INTO #T1 VALUES (10, ‘eeeee’, 5000)

SELECT a.ID1, a.[Name], a.Salary
FROM #T1 AS a
WHERE (@n-1) = (
SELECT COUNT(DISTINCT(b.Salary))
FROM #T1 AS b
WHERE b.Salary > a.Salary)

DROP TABLE #T1
GO

Vamshi
on August 16, 2008 at 5:01 am Vamshi

This can also be done using DENSE_RANK() function of SQL Server 2005.

DECLARE @n INT — Variable for accepting N

SELECT @n = 2 — Get the Second Highest Salary

CREATE TABLE #T1 (ID1 INT, [Name] NVARCHAR(50), Salary INT)
INSERT INTO #T1 VALUES (1, ‘Vamshi’, 1000)
INSERT INTO #T1 VALUES (2, ‘xxxxx’, 2000)
INSERT INTO #T1 VALUES (3, ‘yyyyy’, 3000)
INSERT INTO #T1 VALUES (4, ‘zzzzz’, 4000)
INSERT INTO #T1 VALUES (5, ’sssss’, 5000)
INSERT INTO #T1 VALUES (6, ‘ccccc’, 6000)
INSERT INTO #T1 VALUES (7, ‘ppppp’, 2000)
INSERT INTO #T1 VALUES (8, ‘aaaaa’, 4000)
INSERT INTO #T1 VALUES (9, ‘bbbbb’, 5000)
INSERT INTO #T1 VALUES (10, ‘eeeee’, 5000)

WITH T1 AS
(SELECT (DENSE_RANK() OVER (ORDER BY Salary DESC)) AS RNum, * FROM #T1)
SELECT a.ID1, a.[Name], a.Salary
FROM T1 AS a
WHERE a.RNum = (@n)

Vamshi
on August 23, 2008 at 12:25 am shravan

Hi guys,

My requirement is like this.

I need four data belonging to four latest audit dates for every business.

I used something like…

SELECT TotalUnitCount FROM @TotalUnitsInLot SecondLatestTotalUnits
WHERE (2-1) =
(SELECT COUNT(DISTINCT(LatestTotalUnits.AuditDate))
FROM @TotalUnitsInLot LatestTotalUnits
WHERE LatestTotalUnits.AuditDate > SecondLatestTotalUnits.AuditDate
AND LatestTotalUnits.BusinessId = SecondLatestTotalUnits.BusinessId
AND LatestTotalUnits.BusinessId = TotalUnits.BusinessId
GROUP BY BusinessId
)

This gives me the results I want, but this is really slow. The problem with

SELECT TOP 1 TotalUnitCount
FROM (
SELECT DISTINCT TOP 4 TotalUnitCount
FROM TotalUnitsInLot
ORDER BY TotalUnitCount DESC) a
ORDER BY TotalUnitCount
approach is, if there are only 3 audits for a business, this query still gets the data from 3rd audit and gives me those results. If there is no data for the 4th audit I want to show TotalUnitCount = 0 for the fourth audit. How do I do that ?
on August 28, 2008 at 1:16 am Vamshi

Give the desired Input and Output clearly in the form of a table. then it would be easy for anyone to answer your question.
on September 16, 2008 at 12:23 pm Pawan

Thanks…
on September 16, 2008 at 12:29 pm Pawan

my requirement is to get all the emp names ,their respective manager names in a single query..
like
empno emp_name Emp_mgr
101 xyz 105
102 abc 104
103 red 101
104 kfc 105
105 desa 118

here i need emp_name and his manager name..like
emp mgr
xyz desa
abc kfc
on September 18, 2008 at 10:14 pm Aashish Mangal

Hi Pavan

Below is the answer to your problem

Select a.emp_name,b.emp_name as manager from empman a, empman b where a.emp_mgr = b.empno
I hope this solves your problem
on September 24, 2008 at 6:48 pm Kaushik

Hi,

One question in this query - what if there are multiple employees who has the nth highest salary? How can we get all the employees with the nth highest salary?

Thanks,
Kaushik.
on October 4, 2008 at 2:11 am BUSHIKSHIK

I have about 50000 records inn a table sorted by MODEL_ID
I want to select the last 200 highest MODEL_ID in the table.

How would I do that?

BUSHIKSHIK
on October 4, 2008 at 12:05 pm sam

select min(sal) from (select top 200 sal from table order by sal desc) as a
on October 18, 2008 at 8:15 pm Raman

Pls Help Me
Want SQL Query to retrieve records from two tables but the records which are same in both tables happen once.
on October 30, 2008 at 3:52 pm Jullie

hey raman contact me for other queries.
on November 18, 2008 at 11:30 pm kumari

Hi All,

How to find a highest salary in employee table with emplooyee name.

plz help me on this
on November 18, 2008 at 11:40 pm kumari

Hi,

I tried with this query but i am not satisfied.
could u please explain in detail.
on November 19, 2008 at 1:05 am kumari

This query gives highest salary for employee and employee name.

select empsalry,empname
from empsalr
where empsalry = (select max(empsalry)
from empsalr)
on November 19, 2008 at 1:07 am priyanka

select empsalry,EMPNAME
from empsalr
where empsalry = (select max(empsalry)
from empsalr)
on December 16, 2008 at 2:26 am Cely

Please Help

I am trying to calculate the Median of DATEDIFFERENCE for each Episode. I have succeeded at obtaining the Difference in dates but am unable to extract the nthsmallest by episode.

My Query right now is

SELECT TOP 1 PERCENT DATEDIFF(dd, dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.[Act START DATE], TABLE2.[Act START DATE]) AS DIFFERENCE,
dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.CASENO, dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.[Act START DATE],
TABLE2.[Act START DATE] AS [NEXT Session], OPENCLOSE.OPENID
FROM dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE INNER JOIN
dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE TABLE2 ON
dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.[Act START DATE] = OPENCLOSE.OPENDATE AND
dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.CASENO = OPENCLOSE.CASENO AND TABLE2.[Act START DATE] > OPENCLOSE.OPENDATE
GROUP BY dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.[Act START DATE], TABLE2.[Act START DATE], dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.CASENO,
OPENCLOSE.OPENID
ORDER BY dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.[Act START DATE]

Sorry if I confuse anyone I am new with SQL I handle Crystal Reports XI and minimal SQL. By the way I am running SQL Enterprise 2000.

Thank You
on December 29, 2008 at 4:57 pm Sujit

I want to know query to get highest paid salary for each department from Dept(Dept type,Id) and Employee table(Name,Emp no,..).

Aslo want to know query to list all employee from sales Dept from Employee and Dept Table