Sorting, TOP/LIMIT, and Determinism

"Top" is a business word. ORDER BY is where a query says what it means.

"Top" is a business word, not a database one

SELECT TOP 5 * FROM Employees;

This returns five rows — not the top five of anything. Without an ORDER BY, there is no ranking to be top of, and the engine is free to return rows in whatever order is cheapest to produce. That order can change the moment an index is added, the table grows, or the plan runs in parallel.

Rule

ORDER BY is where a query states what "top" means. TOP or LIMIT without it is not a bug the engine will catch — it's a silently non-deterministic query.

Ties at the cutoff

If two rows tie at the boundary, one is returned arbitrarily, and not necessarily the same one on every run. If the tie matters, either add a tiebreaker column to ORDER BY so the sort is fully deterministic, or use WITH TIES (SQL Server) / RANK() to return every tied row explicitly.

Why deep pagination gets slow

SELECT * FROM Employees ORDER BY Salary DESC OFFSET 100000 LIMIT 10;

The engine still has to produce and discard the first 100,000 rows before returning the next 10 — deep pagination degrades linearly with the offset. The standard fix is keyset pagination: remember the last row's sort key and continue from there instead of counting rows.

SELECT * FROM Employees
WHERE Salary < :lastSalary
ORDER BY Salary DESC
LIMIT 10;

Where TOP/LIMIT sits in the pipeline

It applies after ORDER BY — the very last stage, extending the six-stage model from the Execution Order note. That's precisely why it's meaningless without a sort: there's nothing yet to take the top of.

Try it

SELECT * FROM Employees
ORDER BY Salary DESC
LIMIT 2;