The second highest salary (and the Nth)

Step 1 Solve it yourself first

Tables you will use

employees

What it really tests

Whether you think about ties and about "no answer". LIMIT 1 OFFSET 1 is wrong when two people share the top salary, and many answers return no row at all instead of NULL.

The pattern: Nth highest value

The words "second highest", "Nth largest", "third most recent". The answer is a value, not a person, so ties must collapse: think DENSE_RANK or DISTINCT.

Picture it

flowchart LR
  S["salaries high to low<br/>95000 · 80000 · 70000 · 70000 · 60000 …"] --> R["DENSE_RANK<br/>1 · 2 · 3 · 3 · 4 …"]
  R --> K["keep rank 2<br/>80000"]

The approach, step by step

1. Ask: if two people tie for the highest salary, is the "second highest" the next different amount? (Usually yes.)
2. Rank the distinct salaries from highest down, so ties share a rank and no number is skipped.
3. Keep the salary ranked 2.
4. Wrap it so that "no such salary" gives one row containing NULL, not an empty result — an aggregate like MAX does this.

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

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

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

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Practice

  LeetCode — Top SQL 50 study plan

  free account

• Practice

  DataLemur — second highest salary

  real interview questions, many free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain why LIMIT 1 OFFSET 1 can give the wrong answer here, using a tie example.

   Show answer

   ORDER BY salary DESC LIMIT 1 OFFSET 1 takes the second row, not the second salary. With a tie at the top, the second row has the same salary as the first.

   If Hana's salary were 95,000 too, the sorted salaries would be 95,000, 95,000, 70,000… The second row is 95,000: the highest again, not the second highest (which is 70,000). The query below makes that change and shows the wrong answer.

   It has a second problem: when there is no second row, it returns no rows at all, while the question asks for NULL. SELECT DISTINCT inside a scalar subquery fixes both, and DENSE_RANK = 2 is clearer still.

   sqlCopy

   UPDATE employees SET salary = 95000 WHERE name = 'Hana';
   SELECT salary AS not_second_highest
   FROM employees
   ORDER BY salary DESC
   LIMIT 1 OFFSET 1;

   Try it

   1 row

   not_second_highest
   95000

2. Writefrom a blank page, then run it

   Return the third highest salary, and then the second highest salary within each department.

   Show answer

   The third highest is the same query with WHERE rnk = 3: 70,000 (after 95,000 and 80,000; Ben and Chen share it, and DENSE_RANK counts it once).

   Per department, add PARTITION BY dept_id so each department ranks its own salaries. Starting from departments with a LEFT JOIN, and putting rnk = 2 in the ON, keeps departments with no second salary: Marketing (only Fatima) and HR (nobody) show NULL, as the original question asks.

   sqlCopy

   WITH ranked AS (
     SELECT dept_id, salary,
            DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
     FROM employees
     WHERE dept_id IS NOT NULL
   )
   SELECT d.dept_name, MAX(r.salary) AS second_highest
   FROM departments d
   LEFT JOIN ranked r ON r.dept_id = d.dept_id AND r.rnk = 2
   GROUP BY d.dept_id, d.dept_name
   ORDER BY d.dept_id;

   Try it

   4 rows

   dept_name	second_highest
   Engineering	80000
   Sales	45000
   Marketing	NULL
   HR	NULL

3. Predictsay it before you run it

   If Hana's salary were changed to 95000, what would the solution return?

   Show answer

   70,000. Asha and Hana would both be on 95,000, and DENSE_RANK gives them both rank 1. The next different salary, 70,000 (Ben and Chen), gets rank 2.

   sqlCopy

   UPDATE employees SET salary = 95000 WHERE name = 'Hana';
   WITH ranked AS (
     SELECT salary,
            DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
     FROM employees
   )
   SELECT MAX(salary) AS second_highest
   FROM ranked
   WHERE rnk = 2;

   Try it

   1 row

   second_highest
   70000

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   What does your query return on an empty employees table? Make sure it is one row with NULL.

   Show answer

   One row, with NULL. That is why the solution wraps the result in MAX: an aggregate with no GROUP BY always returns exactly one row, even when no rows match, and MAX of nothing is NULL.

   Without the MAX (SELECT salary FROM ranked WHERE rnk = 2), an empty table would return no rows at all. Below, every employee is deleted first.

   sqlCopy

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

   Try it

   1 row

   second_highest
   NULL

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

I first confirm that ties should collapse, so the second highest is the second different amount. Then I rank salaries with DENSE_RANK in descending order — dense so a tie doesn't skip a number — and take rank 2. I wrap it in MAX so that if there is no second salary I return a single NULL instead of no rows, and for the Nth highest I just change the 2.

Follow-up questions

Tick each check when you can do it without notes