Returning the Top-N Rows Reliably
Why ORDER BY plus LIMIT can be non-deterministic without a tiebreaker.
The Hidden Bug in Top-N Queries
"Give me the top 5 highest-paid employees" feels easy: ORDER BY salary DESC LIMIT 5. But interviewers plant a trap. What if six people share the same salary at the boundary? What if many rows tie?
The core issue is determinism: when the sort key has ties, LIMIT cuts arbitrarily, and the exact rows returned can change between runs. This lesson makes Top-N reliable.
Why ORDER BY + LIMIT Can Be Non-Deterministic
Consider salaries where ranks 4, 5, and 6 are all 50000. ORDER BY salary DESC LIMIT 5 must return exactly 5 rows, so it keeps two of the three tied rows and drops one, but which two is undefined.
Run the query twice, or after the optimizer changes plans, and you may get different people. That non-determinism is the bug interviewers want you to spot.
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;All lessons in this course
- Multi-Column Sorting and NULL Placement
- LIMIT, OFFSET and FETCH FIRST
- Returning the Top-N Rows Reliably
- Sorting by Expressions and Aliases