mediumJoinsGROUP BY and aggregatesWindow functionsSQL
Highest Paid Employee in Each Department
Find the top earner in every department, keeping ties, with a join and a grouped subquery or a window function: a favourite SQL interview question.
SQL
Tables: departments(id, name) and employees(id, name, salary, manager_id, dept_id), where dept_id points to departments.id.
Write a query that returns, for every department, the employee or employees with the highest salary in it. Return three columns in this order: department (the department name), employee (the employee name) and salary. If several people share the top salary in a department, return all of them. Departments with no employees don't appear. Any row order is fine.
Examples
Example 1
Input: departments: (1, Sales), (2, IT) employees: (1, Asha, 7000, -, 1), (2, Ben, 9000, -, 2), (3, Chen, 7000, -, 1), (4, Dev, 6000, -, 2) Output: department | employee | salary Sales | Asha | 7000 Sales | Chen | 7000 IT | Ben | 9000 Why: Asha and Chen tie for the top salary in Sales; Ben is the top earner in IT.
Constraints
- Keep ties: everyone who earns their department's maximum.
- Departments without employees are left out.
- Column order: department, employee, salary.
Hints
Hint 1
First work out each department's maximum salary with GROUP BY dept_id.
Hint 2
Then keep the employees whose (dept_id, salary) matches that maximum.
Hint 3
Or rank employees inside each department with RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) and keep rank 1.
Solution
Show the solution (try it yourself first)
Approach 1: join to the per-department maximum
A derived table computes MAX(salary) per department; joining employees to it on both department and salary keeps exactly the top earners, ties included. A final join adds the department name.
SELECT d.name AS department, e.name AS employee, e.salary
FROM employees e
JOIN departments d ON d.id = e.dept_id
JOIN (
SELECT dept_id, MAX(salary) AS top
FROM employees
GROUP BY dept_id
) m ON m.dept_id = e.dept_id AND m.top = e.salary;Approach 2: a window function (MySQL 8+)
Rank employees within each department and keep rank 1. RANK() keeps ties; ROW_NUMBER() would wrongly keep only one person per department. A window function can't go in WHERE, so rank in a CTE first.
WITH ranked AS (
SELECT e.*, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS r
FROM employees e
)
SELECT d.name AS department, ranked.name AS employee, ranked.salary
FROM ranked JOIN departments d ON d.id = ranked.dept_id
WHERE r = 1;Complexity
Both are O(n log n) for the grouping or sorting; an index on (dept_id, salary) lets MySQL find each maximum quickly.
Found a mistake in this problem, its tests or the solution?