How do you retrieve the second highest salary in SQL Server?

There are several common ways to retrieve the second highest salary in SQL Server. Here are the best approaches:

1. Using MAX() with a subquery

-- Get the second highest salary
SELECT MAX(Salary) AS SecondHighestSalary
FROM Employees
WHERE Salary < (
    SELECT MAX(Salary)
    FROM Employees
);

How it works

• Inner query finds the highest salary.
• Outer query finds the maximum salary smaller than the highest.

2. Using DENSE_RANK() (Recommended)

WITH SalaryRanks AS (
    SELECT 
        Salary,
        DENSE_RANK() OVER (ORDER BY Salary DESC) AS RankNo
    FROM Employees
)
SELECT Salary
FROM SalaryRanks
WHERE RankNo = 2;

Why this is good

• Handles duplicate salaries correctly.
• Easy to extend for 3rd, 4th, nth highest salary.

3. Using ROW_NUMBER()

WITH SalaryRanks AS (
    SELECT 
        Salary,
        ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
    FROM Employees
)
SELECT Salary
FROM SalaryRanks
WHERE RowNum = 2;

Difference

• ROW_NUMBER() gives unique sequence numbers.
• If duplicate salaries exist, this may not return the true second distinct salary.

Example

Suppose the table contains:

• DENSE_RANK() → second highest salary = 8000
• ROW_NUMBER() → second row salary = 8000 (but duplicates affect ranking)

Best Practice

Use DENSE_RANK() when:

• You want the second distinct highest salary.
• Duplicate salaries are possible.

Use MAX() approach when:

• You need a simple interview-style solution.