SQL Subqueries SQL Topic exerciseHardVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

Find Every Top-Paid Employee per Department

Use a correlated MAX scalar subquery scoped to the outer employee department.

  • Joins
  • Subqueries
  • Filtering
  • Sorting
  • Top-N

Exercise brief

Understand the request

Compensation review manager A department review needs every employee tied at the maximum salary, not an arbitrary single winner.

Find the highest-paid employee in each department. Multiple employees tied for the dept maximum should all appear. Use a correlated subquery that returns each dept's MAX(salary). Return employee_id, first_name, last_name, department_name, salary — ordered by department_name, employee_id.

Return

  • Return employee_id, first_name, last_name, department_name, salary in this exact left-to-right order.

Constraints

  • Preserve ties at the department maximum.
  • Do not use LIMIT or assume one winner per department.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

employees

  • employee_idINTEGER
  • first_nameVARCHAR(50)
  • last_nameVARCHAR(50)
  • salaryINTEGER
  • department_idINTEGER

departments

  • department_idINTEGER
  • department_nameVARCHAR(50)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Correlated MAX is the classical 'top per group'. Ties are kept (every employee at the dept max appears).

Hint 2

Window-function alternative: `RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) = 1`.

Hint 3

Equivalent tuple-subquery form: `(department_id, salary) IN (SELECT department_id, MAX(salary) FROM employees GROUP BY department_id)` — see Q20.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT e.employee_id, e.first_name, e.last_name, d.department_name, e.salary FROM employees e INNER JOIN departments d ON e.department_id = d.department_id WHERE e.salary = (SELECT MAX(e2.salary) FROM employees e2 WHERE e2.department_id = e.department_id) ORDER BY d.department_name, e.employee_id;

Why this works

Top-per-group is the most-asked window-function-era question — but it predates window functions. The correlated-MAX form is portable, ties-friendly, and still common in legacy codebases.

Success check

Every employee whose salary equals their department maximum appears once, including ties.

Expected result

Use this output to verify values, aliases, ordering, and row count.

employee_idfirst_namelast_namedepartment_namesalary
104DavidBrownFinance70000
103CarolDavisHR60000
100JohnSmithIT120000
105EmmaTaylorMarketing95000

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.