Why ORDER BY ... LIMIT Fails

Concepts covered: Row Limiting, Sorting Results

Here are the 6 rows on the screen, the table the rest of the lesson uses. Department 10 holds employees 101 to 104, paid from 180,000 down to 120,000 in steps of 20,000, in id order. Department 20 holds 201 at 90,000 and 202 at 85,000. The question is the 3 highest salaries in each department, and the correct result holds 5 rows: 3 from department 10, and both rows from department 20.

The query most hands type first

SELECT department_id, employee_id, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Department 10 holds the 4 largest salaries on the table, so the sort fills all 3 slots from department 10 and department 20 never appears. The query runs without an error and returns 3 well-formed rows, which is what makes it dangerous: a dashboard built on it shows 3 engineers under a heading reading 'top earners by department', and the mistake surfaces weeks later when the Sales manager asks where her team went. LIMIT is 1 counter over the whole result, and SQL has no clause meaning 'take 3 from each group'.

Reading the result back is the check that catches it in the room: 3 rows, and 1 value of department_id repeated down the column. A per-group result shows every department the table has, so a result where the group column holds a single repeated value is the signature of a global sort. Say it when you see it: 'every row here is department 10, so this ranked the table rather than each department.'

Why ORDER BY and LIMIT answer a different question

ORDER BY arranges the rows into 1 sequence and LIMIT cuts that sequence at a fixed position. Both clauses see the table as a single list, and neither knows that department_id divides the list into groups. The question asked for 2 lists, 1 per department, each cut at 3. Sorting by department first, ORDER BY department_id, salary DESC, puts the groups next to each other, and LIMIT 3 still cuts once, so the result is the 3 highest earners of whichever department sorts first. The question needs a cutoff applied once per group, and the sorting clauses have no notion of a group.

The patch that turns 1 query into 1 per department

The next move, once the missing department is noticed, is a filter: WHERE department_id = 20 with LIMIT 3, then the same for department 10, then UNION ALL to glue the 2 results. On 2 departments it produces the right 5 rows. On a real table it needs 1 query per department, the list of departments has to be known when the SQL is written, and a department created next month is missing from the report until someone edits the query. Interviewers hear this patch and ask 'and if there are 400 departments?', which is the question the approach cannot answer.

(SELECT department_id, employee_id, salary
 FROM employees WHERE department_id = 10
 ORDER BY salary DESC LIMIT 3)
UNION ALL
(SELECT department_id, employee_id, salary
 FROM employees WHERE department_id = 20
 ORDER BY salary DESC LIMIT 3);

The patch shows the shape of the right answer. Each branch is a sort and a cutoff inside 1 department. The fix does that for every department in 1 pass without listing them: a counter that runs 1, 2, 3 down each department's rows and resets whenever a new department begins. That counter is a window function, and the next section builds it. The sentence worth saying in the room at this point is: 'LIMIT counts rows for the whole result, so I need a count that restarts per department.'

About this lesson

This section is part of the Top N Per Group: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.