Recap — Why People Get Nth Highest Wrong

The classic prompt: "From the employees table, find the second highest salary."

Sounds trivial. It's interviewer-favourite because three different interpretations all sound right, and the wrong one fails on edge cases.

The naive answer (don't write this)

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

Returns the second-highest distinct salary. Works if there are no ties. Fails when:

• Two employees tie for the highest — this returns the third highest from the rest, not the second highest.
• No second salary exists — returns NULL, which the interviewer may or may not want.

The DENSE_RANK answer (the safe default)

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

DENSE_RANK gives ties the same rank with no gaps. rnk = 2 is the second-highest distinct salary. Handles ties cleanly.

The LIMIT/OFFSET answer (cheaper but distinct-aware)

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

Same intent: second-highest distinct salary. The DISTINCT is the key — without it, two tied first-place salaries make this return the first, not the second.

"What if the second highest doesn't exist?"

The interviewer might want NULL, or might want you to handle it. Wrap with a subquery:

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

The outer SELECT returns NULL when the inner query has no rows. Most interviewers want exactly this.

Parameterising N

WITH ranked AS (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT MAX(salary) FROM ranked WHERE rnk = :n;

DENSE_RANK works for any N. Note: changing to RANK gives different semantics — if the top 3 tie, RANK = 2 won't exist, but DENSE_RANK = 2 returns the next distinct value.

The "per department" variant

The same problem with a twist: "Find the second-highest salary per department."

WITH ranked AS (
    SELECT
        department_id,
        salary,
        DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT department_id, salary FROM ranked WHERE rnk = 2;

PARTITION BY department_id resets ranking per department. Almost always the right SQL when the question has "per X" in it.

Three "gotcha" follow-ups interviewers love

1. "What if salaries can be NULL?" — NULLs sort last with DESC by default in most databases. If you need them excluded, add WHERE salary IS NOT NULL in the CTE.
2. "What if two employees have identical salaries — return both?" — Don't use MAX in the outer SELECT. Return all rows where rnk = 2.
3. "What's the difference between RANK and DENSE_RANK for this question?" — RANK leaves gaps after ties (1, 1, 1, 4). DENSE_RANK doesn't (1, 1, 1, 2). For "Nth highest distinct", DENSE_RANK is right.

Takeaway

Default to DENSE_RANK. It's the answer for ~80% of "Nth highest" variants. The naive MAX < MAX approach silently breaks on ties — never write it in real interviews.