Why can ROW_NUMBER() give the same rows different numbers on repeated runs?

## The guarantee ROW_NUMBER() actually makes `ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)` promises exactly two things: within each partition the values are the integers 1..N with no repeats and no gaps, and rows that the window `ORDER BY` places earlier get smaller numbers. It promises nothing about rows the `ORDER BY` cannot distinguish. Two rows are **peers** when every window ordering expression compares equal on them. If a partition of ten rows contains three peers occupying positions 4, 5 and 6, then all six possible assignments of 4, 5 and 6 to those rows are equally correct results for the same query. The engine picks one; the standard does not say which, and nothing obliges it to pick the same one twice. ## What makes the pick change The assignment falls out of whatever row order the executor happens to produce — a sort, an index scan, a merge of parallel workers, a hash join's output order. Any of those can change without you touching the SQL: statistics get refreshed and the plan flips, a parallel degree changes, an index is added or dropped, rows are reloaded in a different physical order, or the engine is upgraded. That is why the symptom is so often reported as "it was stable for months and then it wasn't". Omitting the window `ORDER BY` is the extreme case. `ROW_NUMBER() OVER ()` numbers rows in an entirely unspecified order — the result is still valid SQL and still 1..N, but the mapping is arbitrary. Some engines also refuse a ranking function without an `ORDER BY` in the `OVER` clause; engines differ here, so check your engine's documentation rather than relying on either behaviour. ## The fix: make the ordering total A window `ORDER BY` is a total order when no two rows in a partition can be peers. In practice that means appending a column or column set that is unique inside the partition: ```sql SELECT id, created_at, ROW_NUMBER() OVER (ORDER BY created_at, id) AS rn FROM events; ``` Since `id` is unique, no two rows tie, and every run produces the same mapping for the same data. The tiebreaker does not have to be meaningful — it only has to be unique and stable. A surrogate primary key is the usual choice; a natural key works if it really is unique and never updated. ## Things that do not fix it - **A query-level `ORDER BY`.** Window functions are evaluated before the final sort, so `... ORDER BY created_at` at the end of the statement changes the order rows are *returned* in, not the numbers already assigned. This is the single most common wrong answer. - **Adding the tie column to `PARTITION BY`.** `PARTITION BY created_at` does not resolve the tie; it restarts the counter inside each timestamp group, so all peers become row 1 — a different result entirely. - **Hoping physical order is insertion order.** Storage order is not a language guarantee, and updates, reorganisation or a different access path can change it at any moment. ## Why it matters downstream Anything that keys off a specific number inherits the instability. A rule that keeps the row numbered 1 keeps an arbitrary member of the tie; a report that labels rows 1..N produces different labels on a re-run; two systems computing the same numbering can disagree while both being correct. If two rows are genuinely indistinguishable under your ordering and the choice between them matters, the ordering is under-specified — that is a modelling gap, not an engine problem, and the tiebreaker column is where you close it. ## Contrast with RANK() and DENSE_RANK() `RANK()` and `DENSE_RANK()` are immune, because they are defined in terms of peer groups: every peer receives the same number by definition, so there is no arbitrary choice left to make. Their values are reproducible even over a non-unique ordering — which is a useful hint when the requirement is "tied rows must be treated identically" rather than "give me exactly one row".