Resources » Code Snippets » SQL

Second Highest and Lowest Salary using SQL Query


Last Updated:   Category: SQL    
Author: 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!

Responses to "Second Highest and Lowest Salary using SQL Query"
Author: raghunath    18 Aug 2009Member 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: Nathan    18 Aug 2009Member 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 D    18 Aug 2009Member 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: Nathan    18 Aug 2009Member 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 2009Member 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: Nathan    19 Aug 2009Member Level: Gold   Points : 0
Thanks Pandian, This will make my resourece more usefull


Author: Ramesh N D    19 Aug 2009Member 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);



Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India