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