Can somebody explain how the following SQL query to get the 'n' highest salary works

Question

SELECT * FROM employee A WHERE 3=(select count(*) +1 from employee B where B.salary > A.salary)

This gets the 3rd highest salary; can somebody explain the logic behind this query and how it works.

Answer 1

In words, this query would be "Select the employee who has two other people with a greater salary." So, the result is the employee with the third highest salary.

Note that this query can fail if there are two or more people with the exact same salary.

Answer 2

This will only work with Distinct Salaries:

For every employee count the number of rows where salary is greater then employee salary. If the count is 2 + 1, return the employee

Therefore it will return the 3rd emplyee.

I would do this with SELECT TOP 1 FROM (SELECT TOP 3 * FROM Employee ORDER BY Salary DESC) a ORDER BY SALARY ASC

Answer 3

This is what is known as a correlated subquery. You can think of it as looping over all the records in the outer query and for each one it evaluates the query in the where clause. (This happens because the query in the where clause references the alias "A" of the outer query)

So for each employee in gets the count of the number of employees with a higher salary.

You could probably implement this logic faster in SQL 2005 & 2008 by using the ROW_NUMBER function.

eg.

WITH SalaryOrder AS
(
SELECT *
   , ROW_NUMBER() OVER(ORDER BY Salary DESC) SalaryRank
FROM employee
)
SELECT * 
FROM SalaryOrder
WHERE SalaryRank = 3

Answer 4

Just to illustrate this with an example, say the salary are as below; repeated the data for the B,

EmpA EmpB

5000 5000

3000 3000

2000 2000

1500 1500

1000 1000

500 500

In the first parse, A.Salary is 5000, so all the Count of salary from B which exceeds 5000 is 0. Add one and its 1. Now this will be the highest salary. In your example, A.Salary is 2000, so all the count of salary from B which exceeds 2000 will be 2, add one and it will be 3. Join 3=3 and A.Salary with value 2000 will get selected.