Thursday, July 28, 2011

Find Nth Highest Salary of Employee in SQL Server

Below are the different ways to find the Nth highest salary of employee from employee table.
Replace 'n' by 2 to get 2nd highest salary of employee and replace by 3 to get 3rd highest salary of employee.

1) This method uses TOP clause.

SELECT TOP 1 Salary
FROM
(
SELECT DISTINCT TOP n Salary FROM Employee ORDER BY Salary DESC
)T
ORDER BY Salary

2) Second method uses CTE along with ranking function


WITH Salaries AS
(
SELECT *, DENSE_RANK () OVER(ORDER BY Salary DESC) as 'RowNum'
FROM Employee
)
SELECT * FROM Salaries
WHERE RowNum = n

3) This method makes use of aggregate functions MAX and COUNT


SELECT MAX(Salary) FROM Employee E1
WHERE 3 =
(
SELECT COUNT(DISTINCT Salary)
FROM Employee E2
WHERE E1.Salary <= E2.Salary
)

Please reply if you can think of any other ways to do the same stuff.


No comments:

Post a Comment