Find the Nth Highest Salary in SQL Server

"Write a query to find the second (or third, or Nth) highest salary" is probably the most asked SQL interview question. It looks easy, but most first attempts fail on one detail: employees with the same salary. In this article we solve it four correct ways, show two common wrong answers and exactly why they fail, then extend it to "per department" and a reusable stored procedure.

Every result below comes from running the queries on SQL Server.

Sample data (with ties on purpose)

CREATE TABLE #Employees (EmpId INT, Name NVARCHAR(30), Department NVARCHAR(20), Salary INT);

INSERT INTO #Employees VALUES
 (1, 'Asha',   'IT',      95000), (2, 'Ravi',   'IT',      72000),
 (3, 'Neha',   'HR',      65000), (4, 'Kiran',  'Finance', 110000),
 (5, 'Meena',  'HR',      58000), (6, 'Arjun',  'IT',      95000),
 (7, 'Sneha',  'Finance', 88000), (8, 'Vikram', 'HR',      65000);

SELECT EmpId, Name, Department, Salary FROM #Employees ORDER BY Salary DESC, EmpId;

Asha and Arjun both earn 95000, and Neha and Vikram both earn 65000. So the distinct salaries from high to low are: 110000 (1st), 95000 (2nd), 88000 (3rd), 72000 (4th), 65000 (5th), 58000 (6th). For N = 3, the correct answer is 88000, and every solution must return that.

Before writing a query in an interview, ask: "If two employees have the same salary, do they count as one position or two?" Almost always the expected answer is "one position" (distinct salaries). Asking this shows the interviewer you think about edge cases.

First, understand ROW_NUMBER, RANK and DENSE_RANK

These three window functions number rows in order. They only differ in how they treat ties:

SELECT Name, Salary,
       ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum,
       RANK()       OVER (ORDER BY Salary DESC) AS Rnk,
       DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRnk
FROM #Employees
ORDER BY Salary DESC, EmpId;

• ROW_NUMBER gives every row a unique number, even for ties (Asha 2, Arjun 3). Which tied row gets which number is not guaranteed.
• RANK gives ties the same number, then skips (2, 2, then 4).
• DENSE_RANK gives ties the same number and does not skip (2, 2, then 3). Its numbers are exactly the "salary positions" we want.

Solution 1: DENSE_RANK (recommended)

DECLARE @N INT = 3;

WITH Ranked AS (
    SELECT Name, Salary,
           DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
    FROM #Employees
)
SELECT Name, Salary
FROM Ranked
WHERE SalaryRank = @N;

This is the best answer in most interviews: it handles ties correctly, it's easy to read, it returns all employees who earn the Nth highest salary (try N = 2: you get both Asha and Arjun), and it extends naturally to "per department" (below).

Solution 2: OFFSET FETCH on distinct salaries

SELECT Salary
FROM (SELECT DISTINCT Salary FROM #Employees) AS s
ORDER BY Salary DESC
OFFSET @N - 1 ROWS FETCH NEXT 1 ROWS ONLY;

"Sort the distinct salaries, skip N − 1 of them, take one." Short and clear, and available from SQL Server 2012. It returns the salary value, not the employees.

Solution 3: TOP with DISTINCT

SELECT MIN(Salary) AS Salary
FROM (SELECT DISTINCT TOP (@N) Salary
      FROM #Employees
      ORDER BY Salary DESC) AS t;

Take the top N distinct salaries (110000, 95000, 88000); the smallest of them is the Nth highest. This works on very old SQL Server versions too.

Solution 4: correlated subquery (works on any database)

SELECT DISTINCT e1.Salary
FROM #Employees e1
WHERE @N - 1 = (SELECT COUNT(DISTINCT e2.Salary)
                FROM #Employees e2
                WHERE e2.Salary > e1.Salary);

Read it as: "find the salary that has exactly N − 1 distinct salaries above it". 88000 has two distinct salaries above it (110000 and 95000), so it's the 3rd highest. It uses only basic SQL, so it works in any database, but the inner query runs for every row, so it's slow on large tables.

Two common wrong answers

Wrong 1: ROW_NUMBER instead of DENSE_RANK

WITH R AS (SELECT Name, Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS rn FROM #Employees)
SELECT Name, Salary FROM R WHERE rn = 3;     -- expected 88000

Look at the RowNum column in the table above: row number 3 is Arjun, 95000. ROW_NUMBER counted Asha and Arjun as two separate positions, so the "3rd highest" is wrong. Worse, with a tie the result can change from one run to the next, because the order of tied rows isn't fixed.

Wrong 2: TOP without DISTINCT

SELECT MIN(Salary) AS Salary
FROM (SELECT TOP (3) Salary FROM #Employees ORDER BY Salary DESC) AS t;

The top 3 rows are 110000, 95000, 95000, so the minimum is 95000, not 88000. Both mistakes come from the same cause: ignoring duplicate salaries. With sample data without ties, both queries look correct, which is exactly why interviewers test them with ties.

Nth highest salary in each department

A very common follow-up: "Find the second highest salary in each department." Add PARTITION BY, which restarts the ranking for every department:

WITH D AS (
    SELECT Name, Department, Salary,
           DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS DeptRank
    FROM #Employees
)
SELECT Department, Name, Salary
FROM D
WHERE DeptRank = 2
ORDER BY Department;

In HR, Neha and Vikram share the top salary (65000), so Meena (58000) is correctly the 2nd. In IT, Asha and Arjun share first place, so Ravi is 2nd. Only the DENSE_RANK solution extends this easily; the TOP and OFFSET solutions would need a separate query for each department.

When N doesn't exist

What if someone asks for the 10th highest salary, but there are only 6 distinct salaries? Wrap the query so it returns NULL instead of no row at all, which is easier for the calling code:

SET @N = 10;
SELECT (SELECT Salary
        FROM (SELECT DISTINCT Salary FROM #Employees) s
        ORDER BY Salary DESC
        OFFSET @N - 1 ROWS FETCH NEXT 1 ROWS ONLY) AS NthSalary;

As a reusable stored procedure

In a real application (for example a payroll report screen), put the logic in a procedure with N as a parameter:

CREATE OR ALTER PROCEDURE dbo.GetNthHighestSalary
    @N INT,
    @Department NVARCHAR(20) = NULL        -- optional filter
AS
BEGIN
    SET NOCOUNT ON;

    IF @N IS NULL OR @N < 1
        THROW 50001, 'N must be 1 or more.', 1;

    WITH Ranked AS (
        SELECT EmpId, Name, Department, Salary,
               DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
        FROM dbo.Employees
        WHERE @Department IS NULL OR Department = @Department
    )
    SELECT EmpId, Name, Department, Salary
    FROM Ranked
    WHERE SalaryRank = @N;
END;

-- Usage
EXEC dbo.GetNthHighestSalary @N = 2;
EXEC dbo.GetNthHighestSalary @N = 1, @Department = 'HR';

Which solution should you give in an interview?

A strong interview answer: write the DENSE_RANK version, mention that ties are counted once, show the PARTITION BY variant if asked about departments, and mention OFFSET FETCH as a shorter alternative when only the salary value is needed.

Interview questions

1. What is the difference between RANK and DENSE_RANK?

Both give tied rows the same rank. RANK then skips numbers (1, 2, 2, 4), while DENSE_RANK continues without gaps (1, 2, 2, 3). For "Nth highest" questions, DENSE_RANK is the right choice.

2. Why is ROW_NUMBER wrong for the Nth highest salary?

It gives tied salaries different numbers, so a duplicate salary takes up two positions. In our data, ROW_NUMBER = 3 returned 95000 instead of 88000.

3. How do you find the second highest salary without window functions?

SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees); This finds the highest salary below the maximum. It handles ties correctly and works in any database, but only for N = 2.

4. How do you get the Nth highest salary for each department?

Use DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) and filter on the rank.

5. What should the query return if N is larger than the number of distinct salaries?

Nothing exists, so a good query returns NULL (by wrapping it in a scalar subquery) or an empty result, and a procedure can also validate N.

Summary

• Always clarify whether tied salaries count as one position; they usually do.
• DENSE_RANK is the best general solution: it handles ties, returns the employees and supports PARTITION BY.
• OFFSET FETCH, TOP + DISTINCT and a correlated subquery also work, if you use DISTINCT salaries.
• ROW_NUMBER and TOP without DISTINCT give wrong answers when salaries repeat.
• Test your query with duplicate values; that's exactly what interviewers do.