Second Highest Salary in Each Department

Why the whole-table answer doesn't carry over

The Second Highest Salary question has a short answer: sort by salary and skip one row. That only works for the whole table.

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

On this data it returns Robert Martinez at 180000. He's the second best paid person in the company, but he's the top earner in Sales, so he isn't the answer for any department. LIMIT works on the final result, and there's no way to make it restart for every group.

The subquery that looks right

The next idea is usually "the highest salary that isn't the maximum", grouped by department:

SELECT department_id, MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees)
GROUP BY department_id
ORDER BY department_id;

It's wrong for four of the five departments. The inner MAX is the company maximum (250000, in Engineering), so only Engineering loses its top earner. Sales reports 180000, its own maximum. The fix is to compare each row with the maximum of its own department, which makes the subquery correlated:

SELECT e.department_id, MAX(e.salary) AS second_highest
FROM employees e
WHERE e.salary < (
  SELECT MAX(m.salary)
  FROM employees m
  WHERE m.department_id = e.department_id
)
GROUP BY e.department_id
ORDER BY e.department_id;

Now the values are right: 150000, 140000, 115000, 95000 and 115000. This version returns the salary, not the person, so you'd need one more join to get names. It also doesn't stretch to the third or fourth highest without nesting another level.

The window function answer

Ranking inside each department does both jobs at once. PARTITION BY e.department_id starts the count over for every department, and the outer query keeps rank 2. That's the solution above, and it returns the people as well as the salaries.

The choice of ranking function matters when salaries tie. Here is a tiny example where two people share the top salary:

WITH pay(dept, name, salary) AS (
  VALUES ('A', 'Ann', 100), ('A', 'Ben', 100), ('A', 'Cal', 90), ('A', 'Dee', 80)
)
SELECT name, salary,
       ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn,
       RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS drnk
FROM pay
ORDER BY salary DESC, name;

ROW_NUMBER gives Ben a 2, so filtering on 2 returns someone tied for first. RANK jumps from 1 to 3, so filtering on 2 returns nothing. Only DENSE_RANK puts Cal, the 90 earner, at 2. The RANK vs DENSE_RANK question walks through this difference in more detail.

No two people in one department share a salary in this sample data, so all three functions happen to agree here. Interviewers ask about ties anyway, so pick DENSE_RANK and say why.

A version without window functions that keeps names

If you have to avoid window functions, count how many distinct salaries in the same department are higher than each person's. The second highest earner has exactly one:

SELECT d.name AS department, e.first_name || ' ' || e.last_name AS name, e.salary
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE (
  SELECT COUNT(DISTINCT m.salary)
  FROM employees m
  WHERE m.department_id = e.department_id AND m.salary > e.salary
) = 1
ORDER BY d.name;

It returns the same five people. Change the 1 to N minus 1 and it finds the Nth highest, which is the idea behind the Nth Highest Salary question.

What to say in the interview

Lead with DENSE_RANK in a CTE and explain the partition. Then mention two choices you made: ties return every person at that salary, and a department with only one distinct salary drops out of the result. Ask whether that's what they want before writing a LEFT JOIN to keep those departments.