Find Every Top-Paid Employee per Job with a Row Subquery

Exercise brief

Understand the request

Job architecture analyst A job-level compensation review needs every employee tied at the maximum for their job code.

Find the highest-paid employee in each job_id. Use the tuple-subquery form `(job_id, salary) IN (SELECT job_id, MAX(salary) FROM employees GROUP BY job_id)`. Return employee_id, first_name, last_name, job_id, salary — ordered by job_id, employee_id.

Data you will use

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

Hints, when you need them

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

Verified SQL answer

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

SELECT employee_id, first_name, last_name, job_id, salary FROM employees WHERE (job_id, salary) IN (SELECT job_id, MAX(salary) FROM employees GROUP BY job_id) ORDER BY job_id, employee_id;

Expected result

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