Find the Highest Salary in Each Department

The amount is easy, the person is the real question

If you only need the number, GROUP BY with MAX is the whole answer:

SELECT department_id, MAX(salary) AS top_salary
FROM employees
GROUP BY department_id;

On this data that gives five rows, from 250000 in department 1 (Engineering) down to 120000 in department 4 (HR). The usual follow-up is "and who earns it?", and that's where answers start to go wrong.

The query that looks right but isn't

A common first try filters on the list of top salaries:

SELECT first_name, last_name, department_id, salary
FROM employees
WHERE salary IN (SELECT MAX(salary) FROM employees GROUP BY department_id);

It returns 10 rows instead of 5. The top salary in Finance is 135000, and David Brown in Engineering earns 135000 too, so he slips in even though he's nowhere near the top of his own department. Lisa Anderson (130000), Jennifer Thomas (120000), Amanda Rodriguez (135000) and Nicole Martinez (120000) get in the same way. The subquery lost the link between a salary and its department.

Another shortcut is putting first_name next to MAX(salary) without grouping by it. SQLite allows that and fills the name from the row with the maximum, but PostgreSQL and SQL Server reject the query, and MySQL rejects it when ONLY_FULL_GROUP_BY is on (the default since 5.7). Even in SQLite, a tie at the top gives you only one of the tied people.

Three correct ways

1. Join back to the grouped maximum. Compute the top salary per department, then join on both columns:

SELECT d.name AS dept_name, e.first_name, e.last_name, e.salary
FROM employees e
JOIN departments d ON d.id = e.department_id
JOIN (SELECT department_id, MAX(salary) AS top_salary
      FROM employees
      GROUP BY department_id) m
  ON m.department_id = e.department_id
 AND m.top_salary = e.salary
ORDER BY dept_name;

This runs on every database, including MySQL 5.7, and keeps ties.

2. Correlated subquery. Keep each employee whose salary equals the maximum of their own department:

SELECT first_name, last_name, department_id, salary
FROM employees e
WHERE salary = (SELECT MAX(salary)
                FROM employees x
                WHERE x.department_id = e.department_id);

It reads almost like the question itself. The inner query depends on the outer row, so mention that it can get slow on large tables without an index on department_id.

3. RANK with PARTITION BY. This is the reference solution above. PARTITION BY restarts the ranking in each department, and rank 1 is the top salary. It's the easiest one to change: rank 2 gives the runner-up, and salary_rank <= 3 gives a top 3.

All three return the same five people here: Alice Chen (Engineering, 250000), Jason Reed (Finance, 135000), Steven Collins (HR, 120000), Kevin Turner (Marketing, 130000) and Robert Martinez (Sales, 180000).

RANK or ROW_NUMBER?

No department in this data has a tie at the top, so ROW_NUMBER would return the same rows today. The difference shows up when two people share the top salary: RANK gives both of them rank 1, while ROW_NUMBER picks one and drops the other without any warning. Use ROW_NUMBER only when the question asks for exactly one row per group, and then add a tie breaker such as ORDER BY salary DESC, id. The RANK vs DENSE_RANK question covers the ranking functions side by side.

What to say in the interview

Ask first whether ties should return everyone, and whether departments with no employees should appear. Then write the RANK version, and mention the join to GROUP BY as the option for databases without window functions. If they push further, explain why salary IN (...) is wrong, using a salary that exists in two departments as the example. For the next step up, try Top 3 Salaries per Department or the Nth Highest Salary.