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
|
| 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 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 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 D 18 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: 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 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 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...
|