PrismitaLEARN · PRACTICE · GROW

How do you find the Nth highest salary (or value) in SQL?

SQL Server T-SQL Intermediate Fresher1–2 Yrs2–3 Yrs SQL Query 1 min read

Quick Answer

The cleanest way is a window function: DENSE_RANK() OVER (ORDER BY Salary DESC) in a CTE, then WHERE rnk = @N. DENSE_RANK handles ties correctly (three people on the top salary are all rank 1, so rank 2 is the genuine second-highest). Alternatively OFFSET (@N-1) ROWS FETCH NEXT 1 ROW ONLY if you don't care about ties.

My go-to is a window function. In a CTE I do DENSE_RANK() OVER (ORDER BY Salary DESC), then filter WHERE rank = N. I use DENSE_RANK rather than ROW_NUMBER so ties are handled — if three people share the top salary they're all rank 1 and rank 2 is the true second-highest. If ties don't matter I'd just do ORDER BY Salary DESC OFFSET N-1 ROWS FETCH NEXT 1 ROW ONLY. There's also the old correlated-subquery trick counting how many distinct higher salaries exist, but it doesn't scale.

  • With ties handled (recommended): WITH R AS (SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk FROM Employees) SELECT DISTINCT Salary FROM R WHERE rnk = @N;
  • Ignoring ties: SELECT Salary FROM Employees ORDER BY Salary DESC OFFSET @N-1 ROWS FETCH NEXT 1 ROW ONLY;
  • Old-school correlated subquery: WHERE (SELECT COUNT(DISTINCT Salary) FROM Employees e2 WHERE e2.Salary > e1.Salary) = @N-1 — works, but slow.
  • Second-highest is just @N = 2.

SQL — Nth highest salary 
DECLARE @N int = 3;

WITH Ranked AS (
    SELECT Salary,
           DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk
    FROM   dbo.Employees
)
SELECT DISTINCT Salary
FROM   Ranked
WHERE  rnk = @N;
All SQL Server questions More on T-SQL