Talk to Webmaster Tony John

### Online Members

srirama
sridhar
Pawan Awasthi
swamy
More...

Resources » Code Snippets » SQL

# Second Highest and Lowest Salary using SQL Query

 Posted Date: 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

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...

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.

 Next Resource: SQL User defined function to format the ZIP code Previous Resource: SQL User Defined Function to swap characters Return to Resources Post New Resource Category: SQL

Post resources and earn money!

More Resources
 Popular Tags Tag posting guidelines Search Tags
SQL query  .