Introduction:
In this article I will explain how to find nth highest salary of the employee in SQL Server.
Description:
In many sites we will find one frequent question that is like how to find 2^{nd} highest salary of employee or how to find 3^{rd} highest salary of employee or how to find nth highest salary of employee in SQL Server. To get required highest salary we are having different alternative ways.
Before write queries to get 2^{nd}, 3^{rd} or nth highest salary of employee first design table in database and give name as “EmployeeDetails”
EmpID  EmpName  Salary 
1  Suresh  7000 
2  Prasanthi  8000 
3  Mahesh  9000 
4  Sai  10000 
5  Nagaraju  11000 
6  Mahendra  12000 
7  Sanjay  13000 
8  Santhosh  14000 
9  Raju  15000 
10  Phani  10000 
11  Kumar  12000 
12  Prasad  9000 
13  Siva  12000 
14  Madhav  14000 
15  Donthi  11000 
Once table design completed now we will see different ways to get 2^{nd}, 3^{rd}, etc or nth highest salary of employee.
Get Highest Salary from Employee Table
Use the below query to get 1^{st}, 2^{nd}, 3^{rd}, 4^{th}, 5^{th} ….etc highest salary of employee
SELECT MAX(Salary) as 'Salary' from EmployeeDetails where Salary NOT IN ( SELECT TOP n1 (SALARY) from EmployeeDetails ORDER BY Salary Desc ) 
Here in above query we need to replace the “n” value with required highest salary of employee
To get 2^{nd} highest salary of employee then we need replace “n” with 2 our query like will be this
SELECT MAX(Salary) as 'Salary' from EmployeeDetails where Salary NOT IN ( SELECT TOP 1 (SALARY) from EmployeeDetails ORDER BY Salary Desc ) 
Output will be like this
Salary 
14000 
To get 3^{rd} highest salary of employee then we need replace “n” with 3 our query like will be this
SELECT MAX(Salary) as 'Salary' from EmployeeDetails where Salary NOT IN ( SELECT TOP 2 (SALARY) from EmployeeDetails ORDER BY Salary Desc ) 
Output will be like this
Salary 
13000 
The above query will help us to get highest salary from EmployeeDetails table suppose if we want to get Employee Details with particular highest salary then we need to use below query to get required details.
Get Employee Details with Particular Highest salary
Use the below query to get Employee Details with 1^{st}, 2^{nd}, 3^{rd}, 4^{th}, 5^{th} ….etc highest salary from table
SELECT * FROM EmployeeDetails e1 WHERE (n1) = ( SELECT COUNT(DISTINCT(e2.Salary)) FROM EmployeeDetails e2 WHERE e2.Salary > e1.Salary) 
Here in above query we need to replace the “n” value with required highest salary of table
To get 2^{nd} highest salary of Employee Details then we need replace “n” with 2 our query like will be this
SELECT * FROM EmployeeDetails e1 WHERE (1) = ( SELECT COUNT(DISTINCT(e2.Salary)) FROM EmployeeDetails e2 WHERE e2.Salary > e1.Salary) 
Output will be like this
EmpID  EmpName  Salary 
8  Santhosh  14000 
14  Madhav  14000 
To get 3^{rd} highest salary of employee then we need replace “n” with 3 our query like will be this
SELECT * FROM EmployeeDetails e1 WHERE (2) = ( SELECT COUNT(DISTINCT(e2.Salary)) FROM EmployeeDetails e2 WHERE e2.Salary > e1.Salary) 
Output will be like this
EmpID  EmpName  Salary 
7  Sanjay  13000 
In this way we can get required employee details with particular highest salary.
If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email. 



30 comments :
very nice post dear...........keep it up great job.
The sqlquery is very confused and difficult in examples
very difficult examples...
HI
SIR..
VERY GOOOD ANSWERS...
Thanks...
IAm NOt getting Sorry
Hi Suresh Sir
myself Zaid Abbas Zaidi
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 Nth Highest Salary then i can
SELECT MIN(salary) FROM emp
WHERE salary IN
(SELECT TOP N salary FROM emp ORDER BY salary DESC);
very nice suresh garu but i have one doubt
Ex: consider ur table ,
now i would like to display total number of employees count who having 8000,9000and 14000 salry and same for all salaries
nice..
very nice toping and usefull for me to get easiest way to get height value of any position.
krishan
Thanks
Good One. Thanks.
But can some one explain me the logic, how it is working internally to produce output?
Amit.
Hi suresh ,
i was going through your post and i have some doubts.i think its wrong.
like i was trying this query.
in my db i have records :
a 1000
b 2000
c 3000
d 5000
e 6000
f 8000
i 9000
j 10000
k 12000
l 14000
m 13000
n 16000
n 17000
when i am running your query its giving me result 6000,where it is supposed to return 14000,the 3rd highest salary???pl help
Find the person who is having second largest salary from Employee(Empname,salary)table can u any one help me.
HI All i given a input like India but i need out put like it i, How i am going to write a c program?
Can u any one help me pls
Superb, thanks lotzzzzzzzzz.
this is ok but this query is too big make it sort and write for better user
Its Very nice and useful
aaaddesffesffessf
Plz help me in this question!!!
How to determine the lowest and highest salary by employee details in employee table
Suresh sir,if i have any doubt i browse for your articles....Thanks
Hi Suresh.. It will be better to mention 'How it Works' while giving query solutions.. it will be helpful for lower level programmers understanding..
Thank u so much..it really helped me
Here are couple of more ways to find second highest salary in SQL
its very usefull...
Hi all,
To find 3rd highest salary we can also use this SQL:
Select max(salary) from
(select distinct salary from employee order by salary desc)
Where rownum < 4;
You can change the number as per n+ 1. Where n is number.
Ur query will give wrong value if try to find 3rd or 4th highest salary because ur list has duplicate value (salary)
to obtain correct result use DISTINCT keyword just before top
try it
Your Post is also heipful,Thanks for it
Amit
SELECT *
FROM EmployeeDetails e1
WHERE (1) = (
SELECT COUNT(DISTINCT(e2.Salary))
FROM EmployeeDetails e2
WHERE e2.Salary > e1.Salary)
THIS WILL GIVE THE SECOND HIGHEST SALARY NOT A 1ST BECAUSE OF WE GAVE HERE WHERE (1) , IF WILL GIVE WHERE (0) THEN EAT WILL DISPLAY 1ST HIGHEST SALARY
select * from emp where sal=(select MAX(sal) as ghy from emp where sal not in(select top 4 sal from emp order by sal desc))
/*for 3rd highest salary n=2, for 2nd highest salary n=1, nth highest salary n=n1*/
SELECT DISTINCT(Salary) FROM employee ORDER BY Salary DESC LIMIT n,1