Why can ORDER BY status alone return tied rows in a different order on each run?
answer
- The clause only orders rows that differ
- Equal rows have no promised arrangement
- SQL does not guarantee a stable sort
- Symptom: a paginated row appears twice
- Fix lives in the key list itself
basics
~20 sORDER BY constrains only the relative order of rows that differ on a sort key. Rows equal on every key you listed may be returned in any order, and SQL promises no stability across runs. Add a unique final key to make the ordering total.
solid answer
~50 sA sort specification list defines a *partial* order when its keys are not unique: SQL guarantees that a row with status 'open' precedes a row with status 'shipped', but it says nothing about the relative order of the fifty rows that all say 'open'. Nothing requires the sort to be stable, and nothing requires the engine to produce the same arrangement twice — a different access path, a parallel scan, a plan change after statistics update, or simply a row being updated can reshuffle the tied block. That is why paginated reads over a non-unique key notoriously repeat a row on one page and skip it on the next: each page is a separate statement with its own arbitrary tie order. The fix is in the query, not the engine: append a key that is unique per row, typically the primary key, so `ORDER BY status, id` becomes a total order and the result is fully deterministic.
code
sql · 5 lines-- Non-deterministic: many rows share a status
SELECT id, status FROM tickets ORDER BY status;
-- Deterministic: the last key is unique per row
SELECT id, status FROM tickets ORDER BY status, id;go deeper
Recall that ORDER BY says nothing about rows that are equal on the sort key, and that adding a unique column such as the primary key as the last key makes the result repeatable.
Explain that a non-unique key list defines only a partial order, that SQL promises no stable sort, and name the ordinary events — plan change, parallel work, data change — that reshuffle ties.
Diagnose the real-world symptom: rows duplicated or skipped across pages, or an export that diffs against itself. Show that the fix belongs in the ORDER BY, and that consistent results in a test environment prove nothing.
Treat deterministic ordering as an interface contract for paginated APIs and exported datasets: which queries must be totally ordered, how that is reviewed, and why relying on incidental plan behaviour is an outage waiting for a statistics refresh.
## The guarantee ORDER BY actually gives ORDER BY promises exactly this: if two rows compare differently on the first sort key, the smaller (or larger, under DESC) comes first; if they tie on that key, the next key decides; and so on down the list. What the standard does **not** promise is any particular arrangement of rows that compare equal on *every* key you wrote. Those rows may appear in any order, and there is no guarantee that two executions of the same text produce the same one. So `ORDER BY status` over a table where hundreds of rows share each status defines only a partial order — a handful of blocks whose internal arrangement is unspecified. ## Why the arbitrary order changes Several ordinary events can rearrange a tied block between runs: - The engine chooses a different way to read the table, so rows arrive at the sort in a different sequence. - The work is split across workers and their partial results are merged in whatever order they finish. - Rows are inserted, deleted or updated between the two runs. - A plan changes after statistics are refreshed, an index is added, or the data grows. You do not need to know which of these fired. The point is that none of them is a bug: the query asked for no particular order among ties, and it got one. ## The symptom people actually hit The classic report is a paginated list where a row shows up on page 1 and again on page 2, while another row never appears at all. Each page is an independent statement. If the ordering is `status` alone, the two statements may lay the tied rows out differently, so the boundary between pages falls in a different place, duplicating one row and losing another. No data was corrupted; the query simply never asked for a repeatable order. A second common symptom is a test that passes locally on a small table and fails in a larger environment — small inputs are often read in one predictable way, so ties happen to come out consistently, and that consistency is mistaken for a guarantee. ## Stability is not promised either In programming languages a *stable* sort preserves the input order of equal elements. SQL makes no such promise: a sort may reorder ties freely, and there is no "previous order" for it to preserve anyway, since the input to the sort is itself an unordered table. Reasoning like "the rows will keep the order the subquery produced" has no basis in the language. ## The fix: make the key list unique Add a final tie-breaker that is unique per row, so no two rows can be equal on all keys: ```sql SELECT id, status, updated_at FROM tickets ORDER BY status, updated_at DESC, id; -- id is unique: total order ``` The ordering is now *total*: for any two rows in the table, the key list decides. Every execution returns exactly the same sequence for the same data, on any engine and any plan. Points of craft: - The tie-breaker must be unique **within the result**, not merely selective. A near-unique timestamp still leaves the occasional duplicate, and a rare non-determinism is worse than a frequent one because it escapes testing. - After a join, the unique column of one table may no longer be unique in the joined result; pick a key list that is unique for the rows you are actually returning. - Direction on the tie-breaker rarely matters for correctness, but pick one and keep it: flipping it changes the output. ## When it does not matter If the consumer aggregates the rows, or displays a set with no notion of position, an arbitrary tie order is harmless and the extra key is noise. Determinism matters when the order is visible and compared over time: paginated APIs, exported files diffed between runs, golden-file tests, and anything a human reads twice and expects to look the same. ## What this is not This is a statement-level property, not an engine defect and not something an index or a configuration setting fixes. An index may make ties *appear* consistent, which is precisely the trap — the consistency is incidental and disappears the day the plan changes. The only durable answer lives in the ORDER BY. ## Checklist - Is the sort key list unique for the rows returned? If not, the result is non-deterministic. - Does anything downstream depend on the order being identical between runs? If yes, append the primary key. - Never treat "it came out right in testing" as evidence that the tie order is fixed.
- Is a SQL sort stable, so ties keep the order they arrived in?No. SQL defines no stable-sort guarantee, and the concept barely applies: the input to the sort is a table, which has no order of its own for the sort to preserve. Any reasoning that depends on ties keeping an earlier arrangement — from a subquery, a previous sort, or insertion sequence — is unfounded. Uniqueness in the key list is the only guarantee.
- The result looks stable in testing. Why is that not evidence?Small tables are often read the same way every time, so ties come out consistently by accident. That consistency is a property of the current plan and data, not of the query. It ends when the table grows, an index is added, statistics are refreshed, or work is split across workers — usually in production, and usually as an intermittent bug.
- After a join, is adding the primary key of one table enough to make the order deterministic?Only if that column is unique in the joined result. A one-to-many join repeats the parent key across child rows, so parent.id no longer identifies a row. Choose a key list that is unique for the rows the query actually returns — often the child's primary key, or both keys together.
Ranking runners by finishing minute leaves everyone who finished in minute 12 in an arbitrary bunch; adding bib number as a second criterion gives every runner one unambiguous place.
saying these in an interview costs you the question
- Assumes tied rows keep insertion or primary-key order
- Calls the changing tie order an engine bug
- Thinks adding an index guarantees a stable order
- Believes SQL sorts are stable like a language sort
- Says a near-unique timestamp is a sufficient tie-breaker