Interview Question of the Week #050 – Query to Retrieve Second Highest Salary of Employee

Question: How do I retrieve the second, or Nth, highest employee salary? First decide whether you mean the Nth distinct salary or the Nth employee after sorting. The original query answers the distinct-salary question and returns every employee tied at that level.

Some interview questions never seem to get old. I keep seeing this one when helping hire developers and DBAs. A candidate who asks what to do with equal salaries is already asking the right question.

DECLARE @Employee TABLE(EmployeeID int,Salary decimal(12,2));
INSERT @Employee VALUES(1,90000),(2,75000),(3,75000),(4,60000),(5,NULL);
DECLARE @N int=2;
-- Original correlated approach: count distinct salaries above this salary.
SELECT E1.EmployeeID,E1.Salary
FROM @Employee AS E1
WHERE E1.Salary IS NOT NULL
AND @N-1=(SELECT COUNT(DISTINCT E2.Salary)
          FROM @Employee AS E2 WHERE E2.Salary>E1.Salary)
ORDER BY E1.EmployeeID;

-- Equivalent distinct-salary ranking, retaining employees tied at that rank.
WITH Ranked AS
(
 SELECT EmployeeID,Salary,DENSE_RANK() OVER(ORDER BY Salary DESC) AS SalaryRank
 FROM @Employee WHERE Salary IS NOT NULL
)
SELECT EmployeeID,Salary FROM Ranked
WHERE SalaryRank=@N ORDER BY EmployeeID;

Both queries return employees 2 and 3, each earning 75000.00. There is one distinct salary above them: 90000. Ties do not consume additional salary levels.

Finding the second highest salary with a correlated query

For a candidate amount, count the distinct higher salaries. Exactly N minus one higher levels means this is the Nth level. The reference to E1.Salary makes the subquery logically correlated; it does not require SQL Server physically to execute a separate subquery once per employee. Read the plan rather than assuming that implementation.

The NULL filter matters. With N=1, the original unfiltered query could also admit employees whose salary was unknown because no comparison is true for their NULL salary. An unknown value is not the highest known salary.

Ranking makes the tie policy visible

DENSE_RANK gives equal amounts the same rank without gaps. ROW_NUMBER instead chooses employee positions and needs a deterministic tie-breaker such as EmployeeID. RANK leaves gaps after ties, so its third rank is not necessarily the third distinct salary.

Use a positive N. When the table has fewer distinct known amounts than requested, these queries return no rows. Neither spelling is automatically faster for every table; data distribution, indexing and the plan decide the work.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.