Top N within a Filter
Return the top 2 highest-paid employees in Engineering (department_id = 1).
- CTEs
- Filtering
- Sorting
- Top-N
Interview brief
Understand the request
Finance analyst The salary review only surfaces the two highest earners in the Engineering department.
Find the top 3 highest-paid IT employees (department_id = 1), with a deterministic tie-breaker. Show first_name, last_name, salary, employee_id.
Return
- Return first_name, last_name, salary, and department_id.
Constraints
- Combine WHERE with ORDER BY and LIMIT.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
first_nameTEXTlast_nameTEXTsalaryDECIMALemployee_idINTEGERdepartment_idINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
WHERE narrows the row set first, ORDER BY sorts what remains, then LIMIT keeps the top few.
Hint 2
The Top-3-IT result is independent of the global Top-3 because the filter happens before the sort.
Hint 3
WHERE department_id = 1 ORDER BY salary DESC, employee_id LIMIT 3
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT first_name, last_name, salary, employee_id FROM employees WHERE department_id = 1 ORDER BY salary DESC, employee_id LIMIT 3;Why this works
Top-N-within-group via WHERE only works for a single group at a time. For "top 3 per department" across all departments at once, you need window functions (ROW_NUMBER OVER PARTITION BY) — covered later.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| first_name | last_name | salary | employee_id |
|---|---|---|---|
| Mike | Johnson | 80000 | 3 |
| Amy | Taylor | 78000 | 8 |
| Kevin | Martinez | 76000 | 13 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics: