SQL query to find Nth highest salary from a salary table

Question

How can I find the Nth highest salary in a table containing salaries in SQL Server?

Answer 1

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

• SQL Fiddle

---

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.