The highest-paid employee in each department

For each department, return the employee (or employees, if tied) with the highest salary. Show the department name, the employee's name and their salary.

What it tests

The most common window-function question: top N per group, including how to handle ties and how to filter on a window function.

The pattern

Top N per group. "In each", "per", "for every" together with "highest", "top 3", "most recent", "first".

How to approach it

  1. Ask: if two people tie for top salary in a department, show both? (Yes here.)
  2. Rank employees within each department by salary, highest first: RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC).
  3. RANK keeps ties together; ROW_NUMBER would pick one at random.
  4. Do the ranking in a CTE, then keep rank 1 outside it, and join departments for the name.

Edge cases to mention

Common mistakes

Not true: SELECT dept_id, name, MAX(salary) … GROUP BY dept_id returns the name of each department's top earner.
In fact: The name is not tied to the maximum: most databases reject the query, and the rest may show any name from the group. Rank the rows with a window function instead.

Part of Unit 8: SQL interview problems, from easy to hard in the free SQL course.