Second Highest and Lowest Salary using SQL Query

Posted Date: 15 Jun 2009 |Updated: 15-Jun-2009 |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

Comments

Author: raghunath18 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 n-1 =
(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 n-1 =
(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary > E2.Salary)

Author: Nathan18 Aug 2009 Member Level: Gold Points : 2

Yes raghunath, you are right

you can also replace the n-1 into n by replacing the < into <=

Author: Ramesh N D18 Aug 2009 Member Level: Gold Points : 1

shall i know what is n-1 or n..
it showing error..

Msg 207, Level 16, State 3, Line 1
Invalid column name 'n'.

Author: Nathan18 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 S18 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 N-1 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: Nathan19 Aug 2009 Member Level: Gold Points : 0

Thanks Pandian, This will make my resourece more usefull

Author: Ramesh N D19 Aug 2009 Member Level: Gold Points : 0

thanks shanmuganathan... gives an good idea...

Guest Author: kanishak05 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);

Return to Article Index

Digital Marketing by SpiderWorks Technologies, Kochi - India. ©