Second Highest and Lowest Salary using SQL Query
Posted Date: 15Jun2009  Last Updated:  Category: SQL  Author: Nathan  Member Level: Gold  Points: 3  

Nowadays, The question "Find the second highest salary of the employee" becomes very popular. We can find the solution of Highest and Lowest salary of the table. Let us consider the following table
Employee Name Salary
Nathan 5000 Ramesh 10000 Sekar 15000 Ram 3000 Kannan 6000
We can get 1st,2nd,3rd,4th.. highest salary of the employee by using the following query. The following query is used to find the second highest salary of the table "Employee" By replacing the "Select top 2 " into "Select top 3" we can get 3rd highest salary of the table "Employee"..
select top 1 * from Employee where Salary in (select top 2 Salary from Employee order by Salary desc)
Out put of the above query will be as follows
Name Salary Ramesh 10000
We can get 1st,2nd,3rd,4th.. lowest salary of the employee by using the following query. The following query is used to find the second lowest salary of the table "Employee" By replacing the "Select top 2 " into "Select top 3" we can get 3rd lowest salary of the table "Employee"..
select top 1 * from Employee where Salary in (select top 2 Salary from Employee order by Salary ) order by Salary desc
Out put of the above query will be as follows
Name Salary Nathan 5000
Thanks Nathan

Did you like this resource? Share it with your friends and show your love!


Author: raghunath 18 Aug 2009  Member Level: Bronze Points : 2 
Correlated sub queries are more generalized to find n'th highest or nth lowest salary.
To find nth highest salary
SELECT * FROM Employee E1 WHERE n1 = (SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary < E2.Salary)
To find 2nd highest salary
SELECT * FROM Employee E1 WHERE 1 = (SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary < E2.Salary)
To find 2nd lowest salary
SELECT * FROM Employee E1 WHERE 1 = (SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary > E2.Salary)
To find nth lowest salary
SELECT * FROM Employee E1 WHERE n1 = (SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary > E2.Salary)

Author: Nathan 18 Aug 2009  Member Level: Gold Points : 2 
Yes raghunath, you are right
you can also replace the n1 into n by replacing the < into <=

Author: Ramesh N D 18 Aug 2009  Member Level: Gold Points : 1 
shall i know what is n1 or n.. it showing error..
Msg 207, Level 16, State 3, Line 1 Invalid column name 'n'.

Author: Nathan 18 Aug 2009  Member Level: Gold Points : 1 
n means 1 st, 2nd, 3rd highest or lowest salary dont give n please give (if you want 2nd highest salary give n=1 for 3rd n=2 like that..

Author: Pandian S 18 Aug 2009  Member Level: Gold Points : 2 
We can avoide TOP by using DENSE_RANK() in SQL Server 2005/2008, If its SQL Server 2000 then N1 is the best way.
LOWESE 3Rd Salary :  ;WITH CTEs AS(SELECT DENSE_RANK() OVER(ORDER BY SALARY) 'Nth',* FROM SAMPLE1)
SELECT Names,Salary FROM CTEs WHERE Nth=3
HIGHEST 3Rd Salary :  ;WITH CTEs AS(SELECT DENSE_RANK() OVER(ORDER BY SALARY DESC) 'Nth',* FROM SAMPLE1)
SELECT Names,Salary FROM CTEs WHERE Nth=3
Cheers

Author: Nathan 19 Aug 2009  Member Level: Gold Points : 0 
Thanks Pandian, This will make my resourece more usefull

Author: Ramesh N D 19 Aug 2009  Member Level: Gold Points : 0 
thanks shanmuganathan... gives an good idea...

Guest Author: kanishak 05 Mar 2012 
u can calculate the second highest salary by using the follwing code select max(sal) from emp where sal not in( select max(sal) rom emp);
