Which rows survive filtering on ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) = 1?
answer
- one number per row, restarting each group
- the partition defines what counts as duplicate
- the window ORDER BY picks the survivor
- 1 means first row of its email group
basics
~20 sExactly one row per distinct email value: the one with the newest created_at. ROW_NUMBER numbers rows 1, 2, 3 inside each email partition in the given order, so number 1 is that partition's first row.
solid answer
~50 s`PARTITION BY email` splits the table into one group per email value, and `ORDER BY created_at DESC` numbers the rows inside each group starting at 1, newest first. Keeping `rn = 1` therefore returns one row per email — the most recent one — and drops the rest. Because a window function is computed after `WHERE`, the numbering has to be produced in a CTE or derived table and filtered in the outer query: ```sql WITH ranked AS ( SELECT c.*, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn FROM customers c ) SELECT * FROM ranked WHERE rn = 1; ``` The whole row survives, not just the key columns, which is exactly what `SELECT DISTINCT email` cannot give you. Emails that appear once still get `rn = 1`, so they are kept too.
code
sql · 10 linesWITH 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;go deeper
Be able to write the two-step shape from memory: number the rows in a CTE, filter rn = 1 outside. Know that PARTITION BY says what a duplicate is and ORDER BY says which copy you keep.
Explain why the numbering must live in a CTE — window functions are computed after WHERE — and why ROW_NUMBER rather than RANK guarantees exactly one survivor per key.
Show judgment about the survivor: a total ordering so the choice is repeatable, and an ORDER BY expression that encodes the real business rule, such as preferring rows with non-null contact details.
Frame recurring deduplication as a symptom: if the same key keeps duplicating, the fix is a uniqueness constraint and an idempotent write path, with the ROW_NUMBER cleanup as the one-off that precedes it.
## 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.
- What changes if you write RANK() instead of ROW_NUMBER() in that query?RANK() gives tied rows the same value, so if two rows for one email share the same created_at, both get rank 1 and the filter returns two rows for that email. The query stops deduplicating. ROW_NUMBER always assigns exactly one 1 per partition, which is why it is the function for this pattern.
- How would you keep the oldest row per email instead of the newest?Flip the window ORDER BY to ascending: ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at ASC). Nothing else changes — the survivor is determined solely by the ordering inside the partition, so keeping the oldest, the smallest id, or the most complete record is just a different ORDER BY expression.
- Why can't you filter on rn inside the same SELECT that computes it?Window functions are evaluated after WHERE, GROUP BY and HAVING, and their output is available only to the SELECT list and ORDER BY. A predicate on rn in the same query has nothing to read yet, so the pattern always needs a CTE or derived table and a filter in the enclosing query.
Think of stacking each customer's paperwork into its own pile, newest on top, then taking only the top sheet from every pile.
saying these in an interview costs you the question
- Says SELECT DISTINCT does the same thing
- Puts WHERE rn = 1 in the same SELECT that computes rn
- Uses RANK() and still expects one row per key
- Omits the window ORDER BY and assumes the newest survives
- Thinks PARTITION BY removes rows rather than numbering them