The Second-Highest Salary SQL Problem (5 Ways to Solve It)

Walk into almost any entry-level data analyst or SQL developer interview in India and there is a strong chance you will be asked to "find the second-highest salary." It looks trivial, but it quietly tests whether you understand subqueries, window functions, ties, and NULL handling. Interviewers love it precisely because there are many valid answers, and your choice reveals how you think.

This post solves it five ways using a simple employees(emp_id, name, dept, salary) table, and explains the trade-offs of each so you can pick the right one under pressure.

Why It Is Trickier Than It Looks

Before writing code, ask the interviewer two questions. They show maturity and steer you away from a wrong answer:

1. What about ties? If three people earn ₹90,000 (the top) and the next earns ₹80,000, is the second-highest ₹90,000 (the second row) or ₹80,000 (the second distinct value)? Almost always they mean the second distinct value.
2. What if there is no second salary? With only one distinct salary, a good solution returns NULL, not an error or an empty result.

Keep these two cases in mind as we go.

Way 1: Subquery with MAX

The cleanest, most readable approach.

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

The inner query finds the overall maximum. The outer query finds the largest salary strictly below it — which is the second-highest distinct value.

• Ties: handled correctly. Multiple people at the top collapse to one MAX.
• No second salary: returns NULL (because no rows satisfy salary < max, and MAX of an empty set is NULL).
• Verdict: great default answer. Portable across every database.

Way 2: LIMIT with OFFSET

If your database supports LIMIT/OFFSET (PostgreSQL, MySQL, SQLite), this reads almost like English.

SELECT DISTINCT salary AS second_highest
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

DISTINCT removes duplicate salaries so ties at the top do not count twice. ORDER BY salary DESC ranks highest first, OFFSET 1 skips the top one, and LIMIT 1 takes the next.

• Ties: handled, thanks to DISTINCT.
• No second salary: returns no rows rather than a NULL row. This is the catch — if the spec demands a NULL, wrap it: SELECT (SELECT DISTINCT salary ... LIMIT 1 OFFSET 1).
• Verdict: elegant, but the empty-result behaviour can fail strict test cases.

SQL Server uses different syntax: OFFSET 1 ROWS FETCH NEXT 1 ROW ONLY after an ORDER BY.

Way 3: DENSE_RANK (the scalable one)

This is the answer interviewers most want to see, because it generalises to the Nth-highest instantly.

SELECT DISTINCT salary AS second_highest
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 2;

DENSE_RANK assigns 1 to the top salary, 2 to the next distinct salary, and so on — with no gaps after ties. So rnk = 2 is exactly the second-highest distinct salary. Change 2 to 5 for the fifth-highest with zero rewrites.

• Ties: handled perfectly — that is the whole point of DENSE_RANK over ROW_NUMBER.
• No second salary: returns no rows (wrap in a subquery for NULL).
• Verdict: best for demonstrating depth and for "find the Nth-highest" follow-ups.

Be careful not to use ROW_NUMBER here. ROW_NUMBER would give tied top earners distinct numbers (1, 2, 3...), so rn = 2 could wrongly return another person at the top salary.

Way 4: Correlated Subquery

A classic that works even in very old databases without window functions.

SELECT DISTINCT e1.salary AS second_highest
FROM employees e1
WHERE 1 = (
    SELECT COUNT(DISTINCT e2.salary)
    FROM employees e2
    WHERE e2.salary > e1.salary
);

For each salary, the subquery counts how many distinct salaries are higher. The second-highest is the one with exactly one salary above it.

• Ties: handled via COUNT(DISTINCT ...).
• No second salary: returns no rows.
• Verdict: great for showing you understand correlated logic, but slow on large tables — the subquery runs once per row. Prefer DENSE_RANK in production.

Way 5: Common Table Expression (CTE)

A CTE makes the DENSE_RANK approach more readable and reusable, which matters in real reporting code.

WITH ranked_salaries AS (
    SELECT
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM employees
)
SELECT DISTINCT salary AS second_highest
FROM ranked_salaries
WHERE salary_rank = 2;

Functionally identical to Way 3, but the named CTE documents intent and is easy to extend — for example, adding PARTITION BY dept to get the second-highest salary per department:

WITH ranked_salaries AS (
    SELECT
        dept,
        salary,
        DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS salary_rank
    FROM employees
)
SELECT dept, MAX(salary) AS second_highest
FROM ranked_salaries
WHERE salary_rank = 2
GROUP BY dept;

That per-department twist is a very common interview follow-up, and the CTE makes it a one-line change.

Which One Should You Use?

• Reading from a real database, want simplicity: Way 1 (MAX subquery). Always returns NULL cleanly when there is no second value.
• Interview, want to impress and handle Nth-highest: Way 3 or Way 5 (DENSE_RANK).
• Legacy database, no window functions: Way 4 (correlated subquery), accepting the performance cost.
• Quick exploration: Way 2 (LIMIT/OFFSET), remembering the empty-result caveat.

Key Takeaways

• Clarify ties and the "no second salary" case before writing a single line.
• Use DENSE_RANK, not ROW_NUMBER, when distinct ranked values matter.
• The MAX subquery is the safest default for guaranteed NULL on edge cases.
• A CTE turns the problem into the more general "Nth-highest per group" with minimal effort.

Master all five and you will never be caught off guard by this question — or its many variations.

Related: SQL + Power BI Challenge for Data Analysts · Practise SQL problems

Don't just read. Prove your skill on DevWithData.