Monday, July 4, 2011

Nth max salary


Nth max salary

declare @N int
set @N = 2

SELECT Salary FROM dbo.Employee E1
WHERE @N = (SELECT Count(Salary) FROM dbo.Employee E2 WHERE E1.Salary <= E2.Salary)

USING Joins:

SELECT A.Salary, count(1) FROM dbo.Employee A
LEFT OUTER JOIN dbo.Employee B ON A.Salary <= B.Salary
GROUP BY A.Salary
HAVING count(1) = 1

No comments:

Post a Comment