skip to content

A ROW_NUMBER dedup keeps a different survivor each run — why, and how do you fix it?

level: middleimportance: should knowfreq 50%

answer

  1. equal rows have no defined order
  2. the query never said which of the tied rows wins
  3. add something unique to the ordering
  4. think about where NULLs sort too

basics

~20 s

The window ORDER BY does not order each partition totally, so tied rows may be numbered in any order and the engine is free to pick differently between runs. Append a unique column, such as the primary key, to that ORDER BY.

solid answer

~50 s

`ROW_NUMBER` assigns 1 to *some* row among those tied on the window `ORDER BY`; nothing in the standard says which one, so a different plan, a different row order or a re-run can produce a different survivor. If you then delete `rn > 1`, you have destroyed data by a coin flip. The fix is a **total** ordering inside the partition: append a column that is unique within it, normally the primary key — `ORDER BY created_at DESC, customer_id DESC`. Two related traps: an ordering column that is itself NULL, since where NULLs sort is implementation-defined unless you write `NULLS FIRST` or `NULLS LAST`; and an ordering that does not express the real rule. If "best" means the most complete record, encode that first: `ORDER BY CASE WHEN phone IS NULL THEN 1 ELSE 0 END, created_at DESC, customer_id`.

code

sql · 5 lines
sql
-- Non-deterministic: rows sharing created_at may swap places between runs
ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC)

-- Deterministic: the unique key breaks every remaining tie
ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC, customer_id DESC)

go deeper

for a junior

Remember that rows tied on the window ORDER BY can be numbered in any order, and that adding the primary key at the end of that ORDER BY makes the result the same every time.

for a middle

Explain that a partition needs a total ordering for ROW_NUMBER to be deterministic, and that NULL placement in the ordering column is implementation-defined unless NULLS FIRST or NULLS LAST is written.

for a senior

Show that you separate repeatability from correctness: encode the real survivor rule as leading CASE expressions in the window ORDER BY, and treat an arbitrary survivor in a delete as data loss, not cosmetics.

for a principal

Own the policy: which record wins a merge is a data-governance decision, so it should be written down once, implemented in one place, and applied identically by the cleanup job and the ingestion path that will meet the same conflict tomorrow.

## The symptom A deduplication query is run twice over unchanged data and returns a different row for some keys, or a nightly cleanup keeps the record with a phone number one night and the record without it the next. Nothing is broken in the engine — the query simply never said which of several equally-ranked rows should win, and an unspecified choice is allowed to vary. ## Why ties leave the outcome open `ROW_NUMBER()` numbers the rows of a partition 1, 2, 3 in the order given by the window `ORDER BY`. When two rows compare **equal** on every ordering expression, they are peers, and the standard does not define which peer is numbered first. In practice the answer falls out of the physical order the operator happened to receive rows in, which can change with a different access path, after a data reorganisation, after statistics change, or simply between parallel workers. Relational tables have no inherent row order, so "the one that was inserted first" is not something the query can rely on unless a column records it. This is harmless in a report and dangerous in a cleanup, because the deduplication `DELETE` turns an arbitrary choice into permanent data loss. ## Making the ordering total An ordering is **total** within a partition when no two rows of that partition tie on the full list of ordering expressions. The reliable way to get that is to append a column that is unique per row — usually the primary key: ```sql ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC, customer_id DESC) ``` Now, if two rows share `created_at`, the larger `customer_id` wins, every time, on every engine. The tiebreaker column does not have to be meaningful; it only has to be unique and stable. What it must not be is a column that can also tie, such as a status flag or a date truncated to the day. ## The NULL trap in the ordering column If the ordering column is nullable, some rows sort by a value and some by nothing. The SQL standard says nulls are treated as either greater than or less than all non-null values, but **which** is implementation-defined, so `ORDER BY last_login DESC` can put the never-logged-in rows first on one engine and last on another. Two rows both NULL are peers again. Say what you mean: ```sql ORDER BY last_login DESC NULLS LAST, customer_id ``` Or fold the rule into the expression, which also works where `NULLS LAST` is unavailable: ```sql ORDER BY CASE WHEN last_login IS NULL THEN 1 ELSE 0 END, last_login DESC, customer_id ``` ## Deterministic is not the same as correct A total ordering guarantees you get the *same* survivor every run. It does not guarantee you get the *right* one. Deduplication almost always has a business rule behind it — keep the verified record, keep the one with contact details, keep the one whose source system ranks higher — and that rule belongs in the window `ORDER BY`, ahead of the mechanical tiebreaker: ```sql ROW_NUMBER() OVER ( PARTITION BY LOWER(email) ORDER BY CASE WHEN verified_at IS NULL THEN 1 ELSE 0 END, CASE WHEN phone IS NULL THEN 1 ELSE 0 END, created_at DESC, customer_id DESC) ``` Read top to bottom, this says: prefer verified rows, then rows that have a phone number, then the newest, and break any remaining tie by the highest id. Every clause after the first is only consulted when the ones above it tie, which is exactly how a human would describe the rule. ## What an interviewer is listening for That you recognise the non-determinism as a property of the query rather than a bug in the database; that you name a unique trailing column as the mechanical fix; that you mention NULL placement rather than assuming a default; and that you separate "repeatable" from "the survivor the business wants". A weak answer blames the engine, suggests `ORDER BY` in the outer query (which changes only the presentation order, not the numbering), or proposes `DISTINCT` as a workaround.

  • Does adding ORDER BY to the outer query fix the non-determinism?
    No. The outer ORDER BY sorts the rows that survived; it has no influence on how ROW_NUMBER numbered them inside each partition. The numbering is decided entirely by the ORDER BY written inside OVER, so that is the only place the tiebreaker helps.
  • The tiebreaker column is a nullable timestamp. What extra care does it need?
    Where NULLs sort relative to real values is implementation-defined, so the same query can favour never-populated rows on one engine and penalise them on another, and two NULLs still tie with each other. Spell it out with NULLS LAST, or sort on a CASE expression that maps NULL to a fixed position, and keep a unique column at the end.
  • How would you encode 'keep the most complete record' rather than 'keep the newest'?
    Put the completeness rule first in the window ORDER BY as CASE expressions that score the missing fields, then fall back to recency, then to the primary key. Each later expression is consulted only when the earlier ones tie, which mirrors how the rule is stated in prose.

saying these in an interview costs you the question

  • Blames the database for returning rows in a different order
  • Adds ORDER BY to the outer query and calls it fixed
  • Uses another non-unique column as the tiebreaker
  • Assumes NULLs always sort last in a descending order
  • Thinks ROW_NUMBER follows insertion order

context

resolving links…