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

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 (https://blog.sqlauthority.com), Pravin Phatangare

## SQL SERVER – Avoid Using Function in WHERE Clause – Scan to Seek

• Satinder Mehra
May 8, 2012 9:39 pm

.
.
. 2nd highest value ????????????????

select max(salary) as ‘second highest salary’ from tbemployee where salary not in

(
select top 2 (salary) from tbemployee order by salary desc
)

• Satinder Mehra
May 8, 2012 9:56 pm

Respected EveryOne

how can i fetch tha salary between 5 to 10 highest salary from a table ???????

• Looat at point 4 pagination here

• Purna Magum
June 21, 2012 5:13 pm

Create table #EMP(ENO INT,ENAME NVARCHAR(20),SAL FLOAT)

INSERT INTO #EMP
SELECT 1,’VINAY’,15000.00 UNION
SELECT 2,’KESAV’,52890.00 UNION
SELECT 3,’HANU’,24000.00 UNION
SELECT 4,’ARUN’,42200.00 UNION
SELECT 5,’KISHORE’,25000.00 UNION
SELECT 6,’VINOD’,32000.00 UNION
SELECT 7,’SENDIL’,22890.00 UNION
SELECT 8,’RAKESH’,26400.00 UNION
SELECT 9,’SURYA’,25200.00 UNION
SELECT 10,’SIVA’,28900.00

SELECT * FROM #EMP

SELECT * FROM #EMP ORDER BY SAL DESC

–TO GET ONLY TOP 5-10 HIGHEST SALARIES
SELECT TOP 5 SAL FROM #EMP WHERE ENO NOT IN
(SELECT TOP 5 ENO FROM #EMP ORDER BY SAL DESC)
ORDER BY SAL DESC

–TO GET ONLY TOP 5-10 HIGHEST SALARIED EMPLOYEE DETAILS
SELECT TOP 5 * FROM #EMP WHERE ENO NOT IN
(SELECT TOP 5 ENO FROM #EMP ORDER BY SAL DESC)
ORDER BY SAL DESC

DROP TABLE #EMP

• Purna Magum
June 21, 2012 5:46 pm

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY SAL DESC) AS RN,* FROM #EMP) AS T WHERE T.RN BETWEEN 6 and 10
–also you can use

• “select max(salary) from (select distinct top 5 salary from Emp_Id order by salary desc) a “…why we have used ‘a’ after the subquery..will anyone pls tell me..

• Purna Magum
June 21, 2012 5:04 pm

Create table #EMP(ENO INT,ENAME NVARCHAR(20),SAL FLOAT)
INSERT INTO #EMP
SELECT 1,’VINAY’,15000.00 UNION
SELECT 2,’KESAV’,52890.00 UNION
SELECT 3,’HANU’,24000.00 UNION
SELECT 4,’ARUN’,42200.00 UNION
SELECT 5,’KISHORE’,25000.00

SELECT * FROM #EMP

SELECT TOP 1 SAL FROM #EMP WHERE ENO NOT IN
(SELECT TOP 1 ENO FROM #EMP ORDER BY SAL DESC)
ORDER BY SAL DESC– To get only 2nd highest salary

SELECT TOP 1 * FROM #EMP WHERE ENO NOT IN
(SELECT TOP 1 ENO FROM #EMP ORDER BY SAL DESC)
ORDER BY SAL DESC– To get the details of 2nd highest salaried emploee details

DROP TABLE #EMP

• Sachin tanwar
July 3, 2012 2:08 pm

how one can find the third highest salary ,with using subqueries?

• Purna Magum
July 4, 2012 10:47 am

SELECT TOP 1 * FROM #EMP WHERE ENO NOT IN
(SELECT TOP 2 ENO FROM #EMP ORDER BY SAL DESC)
ORDER BY SAL DESC– To get the details of 2nd highest salaried emploee details

even using Row_Number you can do this

SELECT ENO,ENAME,SAL FROM
(SELECT Row_Number() OVER (ORDER BY SAL DESC) AS RN,*
FROM #EMP) E WHERE E.RN =3

– To get the details of 3nd highest salaried emploee details

• Hi Purna,

Your answer will not work when there is multiple record with same sal
for example:
if there is records like:

ENO ENAME SAL
1 ABC 3000
2 XYZ 3000
3 PQR 2000
4 DEF 1000
This time it will give record 3 as answer
I have solution of it:
with result AS
(
select maths,RANK() over (order by maths desc) as row from tblResult group by maths
)
select * from result r inner join tblResult t on r.maths = t.Maths where ROW = 3

Thanks

• Purna Magum
July 6, 2012 8:25 pm

Hi Mr.DK,

Absolutely correct. But I’ll update my query now to this

Create table #EMP(ENO INT,ENAME NVARCHAR(20),SAL FLOAT)
INSERT INTO #EMP
SELECT 1,’VINAY’,3000.00 UNION
SELECT 2,’KESAV’,3000.00 UNION
SELECT 3,’HANU’,2000.00 UNION
SELECT 4,’ARUN’,2000.00 UNION
SELECT 5,’Sanood’,5000.00 UNION
SELECT 6,’Vijay’,1000.00

SELECT ENO,ENAME,SAL FROM
(SELECT Dense_Rank() OVER (ORDER BY SAL DESC) AS RN,* FROM #EMP) E WHERE E.RN =3

Even this will work perfectly then Rank() function when there is multiple values with same Sal then Rank will escape some values

result:
ENO ENAME SAL
3 HANU 2000
4 ARUN 2000

• Hi your query is right giving correct i have another solution
with result AS
(
select maths,RANK() over (order by maths desc) as row from tblResult group by maths
)
select * from result r inner join tblResult t on r.maths = t.Maths where ROW = 3

Thanks

• Sudheer Budati
July 10, 2012 12:56 pm

Thanks for your Queries. These r very use full. Really thanks to one and all.

Regards

Sudheer.

• select top 1 * from ( select top 7 * from city order by city_id desc) as city_id order by city_id asc

• Vijesh Jain
July 25, 2012 3:22 pm

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

what does this ‘ a ‘ means here.

Q2. How to auto number the rows in a table in Oracle SQL*9 ?

Q3. How to execute the query to find the 3rd highest salary of a employee query in Oracle SQL*9 ?

Q3. Write a sql query to display employee name with grade name as a column. If a employee having salary greater than or equal to 5000 than the grade = GOLD otherwise SILVER ?

Hint : u have a Employee(empid,empname,salary) table

Vijesh Jain

• you can do it by below query as well
suppose i have to find out the fifth highest salary of an employee
then

select Salary from
(select *,DENSE_RANK() over (order by Salary desc) as ‘num’ from tblEmployee) t1
where num=5

it will return the fifth highest salary of an employee

• display top 2 salary holder’s from emp table

• SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)

• select max(sal) from emp where sal<(select max(sal) from emp);

• sir please tell me suppose election polloing application that different categories
and find out highest votes in one by one categories

• select top 1 salary
from ( select distinct top 2 salary
from Salary order by salary desc)a order by salary what is meaning of a after from () a order by salary

• SELECT MIN (SALARY )
FROM UEXAMPLE1
WHERE SALARY IN (SELECT DISTINCT TOP 4 SALARY FROM UEXAMPLE1 ORDER BY SALARY DESC),

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

both the queries are not woking in oracle it gives the error FROM keyword not found where expected

• (TableName=Student, ColumnName=Mark) :=>

select *from student where mark=(select mark from(select row_number() over (order by mark desc) as t,mark from student group by mark) as td where t=2)