learn deep. ship safe.Practice testsFollow ByteMunSubscribe

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?

Written by Published Tests checked by Deepak
SQL · standard syntax, runs on SQLite
Case 1
Table departments
idname
1Sales
2IT
Table employees
idnamesalarymanager_iddept_id
1Asha7000NULL1
2Ben9000NULL2
3Chen7000NULL1
4Dev6000NULL2
Expected (3 rows)
SalesAsha7000
ITBen9000
SalesChen7000
Case 2
Table departments
idname
1Ops
2Legal
Table employees
idnamesalarymanager_iddept_id
1Eli5000NULL1
2Fay4000NULL1
Expected (1 row)
OpsEli5000

Run checks these 2 samples. Submit checks all 6 tests, including hidden ones.

  1. Daily Sales Running TotalWindow functions · GROUP BY and aggregates · SQL
    medium
  2. Find Duplicate EmailsGROUP BY and aggregates · SQL
    easy
  3. easy
  4. easy