Department Highest Salary
Problem
Write a SQL query to find employees who have the highest salary in each department.
SQL: SELECT d.name AS Department, e.name AS Employee, e.salary AS Salary FROM Employee e JOIN Department d ON e.departmentId = d.id WHERE (e.departmentId, e.salary) IN (SELECT departmentId, MAX(salary) FROM Employee GROUP BY departmentId);
(Joe, IT, 70000), (Henry, IT, 80000), (Sam, Sales, 60000), (Max, Sales, 90000)[(IT, Henry, 80000), (Sales, Max, 90000)]SELECT d.name AS Department, e.name AS Employee, e.salary AS Salary
FROM Employee e JOIN Department d ON e.departmentId = d.id
WHERE (e.departmentId, e.salary) IN
(SELECT departmentId, MAX(salary) FROM Employee GROUP BY departmentId);Explanation
We need the top earner(s) in each department. The plan splits into two parts: first figure out the highest salary per department, then keep employees whose salary matches it.
The subquery SELECT departmentId, MAX(salary) FROM Employee GROUP BY departmentId does the first part. GROUP BY buckets employees by department and MAX picks the biggest salary in each bucket, giving one (departmentId, maxSalary) pair per department.
The outer query then keeps any employee whose (departmentId, salary) pair is IN that result set. Using the pair (not just salary) ensures we only match the maximum within the same department. A JOIN to Department turns the department id into its readable name.
Example: IT has Joe(70k) and Henry(80k); Sales has Sam(60k) and Max(90k). The per-department maxes are IT=80k and Sales=90k, so the result is (IT, Henry, 80000) and (Sales, Max, 90000). Ties keep everyone at the max.
Grouping scans the table once and the membership check is a fast lookup, so this stays efficient.