Why can SELECT * FROM orders LIMIT 10 return different rows each time it runs?

## Results are unordered unless you order them SQL defines a query result as a multiset of rows. Nothing in the language says rows come back in insertion order, primary-key order, or physical storage order. `ORDER BY` is the single construct that imposes an order on the rows a query returns, and it is the only guarantee you get. Most of the time that formal fact is harmless, because the client reads the whole result and does its own thing with it. `LIMIT` is what turns the formality into a bug: once you keep only part of the result, an unspecified *order* becomes an unspecified *set of rows*. "Any order" and "any ten rows" are very different problems. ```sql -- unspecified: which ten rows is entirely up to the engine SELECT * FROM orders LIMIT 10; -- specified: the ten oldest, ties broken by id SELECT * FROM orders ORDER BY created_at, id LIMIT 10; ``` ## Why the answer changes over time The rows an unordered query emits first are an artefact of how it was executed, and that can change without anyone touching the SQL: a new index gives a different access path, a rewrite or updated statistics change the chosen plan, rows are updated and relocated, parallel execution interleaves producers differently, or the query runs against a replica whose physical layout differs. The dangerous property is that none of these show up in testing. A small development table with no concurrent writes will hand back the same ten rows all day, so the defect ships and surfaces later as "the dashboard shows different top customers on refresh". ## Ordering that is not total is still non-deterministic Adding any `ORDER BY` is not automatically enough. Consider a table where thousands of rows share a `created_on` date: ```sql SELECT id FROM events ORDER BY created_on LIMIT 10 OFFSET 10; ``` All rows with the same `created_on` are **peers** as far as the sort is concerned, and SQL says nothing about the relative order of peers. Two consecutive requests can legitimately order the tied block differently, so a row can appear on page 2 and again on page 3, while another appears on neither. This is the same class of bug as having no `ORDER BY` at all, just narrowed to the ties. The fix is to make the ordering **total** — no two rows can compare equal on the full key list. In practice: append a column that is unique within the table, usually the primary key, and match its direction to the leading key so the intent stays readable. ```sql SELECT id FROM events ORDER BY created_on DESC, id DESC LIMIT 10 OFFSET 10; ``` Now every row has exactly one position in the ordering, and the slice is reproducible for a given data set. ## Where this bites in practice - **Paging.** Page boundaries land in the middle of a tied block, so users see duplicates and gaps between pages. - **"Give me a sample."** `LIMIT 100` with no order is often used as a sample; it is not a random sample, and it is not a stable one either. If reproducibility matters, order by a key. - **Top-N reporting.** `LIMIT 5` without `ORDER BY` on the measure is not "the top five", it is five arbitrary rows. - **Tests.** A test that asserts on the first row of an unordered limited query is flaky by construction; it will pass until an index is added. ## Sanity checks before you ship a limited query 1. Is there an `ORDER BY` at all? If not, can you honestly say "any rows will do"? If not, add one. 2. Can two different rows compare equal on the full sort key? If yes, append a unique tie-breaker. 3. Does the caller need the *same* rows across separate requests? Determinism per statement is not the same as page stability while other sessions insert and delete — that is a separate property of offset-based paging, and needs a separate answer. The short version an interviewer wants to hear: `LIMIT` slices an order, it does not create one, and the slice is only reproducible when the order is total.