Which rows survive filtering on ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) = 1?

## The task this pattern solves A table has accumulated more than one row per business key — several `customers` rows for the same `email`, several imported events for the same `external_id`. You want **one row per key**, and you want to choose *which* one survives (usually the newest, sometimes the oldest or the most complete). `SELECT DISTINCT` cannot do this: it only removes rows that are identical in every selected column, and duplicate records normally differ in `id`, `created_at` and other columns. `GROUP BY email` cannot do it either, because grouping collapses rows and you then have no legal way to project the other columns of one specific row. Numbering rows inside each duplicate group solves both problems at once. ## What ROW_NUMBER does here `ROW_NUMBER()` is a window function: it is evaluated over a *window* of rows and returns a value for every input row without collapsing anything. - `PARTITION BY email` splits the input into independent groups, one per distinct `email` value. Numbering restarts at 1 in each partition. - `ORDER BY created_at DESC` defines the order *inside* a partition. The first row in that order gets 1, the next 2, and so on. - The result is dense and gapless per partition — `ROW_NUMBER` never repeats a number inside a partition and never skips one, which is precisely why it is the right function for deduplication. So `rn = 1` marks, for every email, the row that sorts first — the newest `created_at`. A key that occurs only once still produces one row with `rn = 1`, so nothing legitimate is lost. ## Why the two-step shape Window functions are evaluated after `FROM`, `WHERE`, `GROUP BY` and `HAVING`, and their results are only available to the `SELECT` list and `ORDER BY`. Writing `... WHERE ROW_NUMBER() OVER (...) = 1` is invalid, and referencing the alias `rn` in the same query's `WHERE` is invalid too. The portable shape is therefore always two levels: compute `rn` in a CTE or derived table, filter it in the enclosing query. ```sql WITH ranked AS ( SELECT customer_id, email, created_at, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn FROM customers ) SELECT customer_id, email, created_at FROM ranked WHERE rn = 1; ``` ## Why ROW_NUMBER and not RANK or DENSE_RANK `RANK()` and `DENSE_RANK()` give **tied rows the same number**. If two rows for one email share the same `created_at`, both get rank 1 and `rn = 1` returns two rows for that email — the query no longer deduplicates. `ROW_NUMBER` breaks ties arbitrarily but always produces exactly one row numbered 1 per partition. "Arbitrarily" is the catch: if the window `ORDER BY` does not order the partition totally, which tied row wins is not determined by the query. When it matters which duplicate survives, append a unique column such as the primary key to the window `ORDER BY`. ## Changing which row survives The survivor is entirely a function of the window `ORDER BY`: - newest wins: `ORDER BY created_at DESC` - oldest wins: `ORDER BY created_at ASC` - smallest key wins: `ORDER BY customer_id` - prefer rows that actually have a phone number, newest first: `ORDER BY CASE WHEN phone IS NULL THEN 1 ELSE 0 END, created_at DESC` And the definition of "duplicate" is entirely a function of `PARTITION BY`: partition by `LOWER(email)` if case-insensitive matching is the business rule, or by several columns for a composite key. ## Common mistakes Expecting `SELECT DISTINCT` to be equivalent; putting `rn = 1` in the same query's `WHERE`; using `RANK()` and being surprised by two survivors; omitting the window `ORDER BY` entirely, which leaves the survivor completely unspecified; and assuming the pattern removes anything — it is a `SELECT`, so it only *shows* the deduplicated set until you write a `DELETE` or rebuild the table. Window functions are standard SQL since SQL:2003, so this pattern is portable across current engines; older MySQL (before 8.0) and older SQLite (before 3.25) predate window-function support and need a different approach.