How can I find the Nth highest salary in a table containing salaries in SQL Server?
Advertisement
Answer
You can use a Common Table Expression (CTE) to derive the answer.
Let’s say you have the following salaries in the table Salaries:
EmployeeID Salary
--------------------
10101 50,000
90140 35,000
90151 72,000
18010 39,000
92389 80,000
We will use:
DECLARE @N int
SET @N = 3 -- Change the value here to pick a different salary rank
SELECT Salary
FROM (
SELECT row_number() OVER (ORDER BY Salary DESC) as SalaryRank, Salary
FROM Salaries
) as SalaryCTE
WHERE SalaryRank = @N
This will create a row number for each row after it has been sorted by the Salary in descending order, then retrieve the third row (which contains the third-highest record).
For those of you who don’t want a CTE (or are stuck in SQL 2000):
[Note: this performs noticably worse than the above example; running them side-by-side with an exceution plans shows a query cost of 36% for the CTE and 64% for the subquery]:
SELECT TOP 1 Salary
FROM
(
SELECT TOP N Salary
FROM Salaries
ORDER BY Salary DESC
) SalarySubquery
ORDER BY Salary ASC
where N is defined by you.
SalarySubquery is the alias I have given to the subquery, or the query that is in parentheses.
What the subquery does is it selects the top N salaries (we’ll say 3 in this case), and orders them by the greatest salary.
If we want to see the third-highest salary, the subquery would return:
Salary ----------- 80,000 72,000 50,000
The outer query then selects the first salary from the subquery, except we’re sorting it ascending this time, which sorts from smallest to largest, so 50,000 would be the first record sorted ascending.
As you can see, 50,000 is indeed the third-highest salary in the example.