Deduplicate Rows with ROW_NUMBER — SQL

Solving tips

Deduplication means one row per key: PARTITION BY the dedup key, ORDER BY your recency/tie-break rule, and keep the row where ROW_NUMBER = 1.
Put ROW_NUMBER in a subquery or CTE, then filter on it — you cannot reference a window alias inside WHERE in the same SELECT.
Make ORDER BY fully deterministic (add a unique tie-break like id) or which duplicate survives becomes arbitrary.

A contacts table has accumulated duplicate rows for the same email. You need to keep exactly one record per email — the most recently updated one — and drop the rest.

Schema

CREATE TABLE contacts (
    id         INT PRIMARY KEY,
    email      TEXT,
    full_name  TEXT,
    updated_at TIMESTAMP
);

id	email	full_name	updated_at
1	ann@x.com	Ann Lee	2024-01-10 09:00:00
2	bob@x.com	Bob Ray	2024-01-11 10:00:00
3	ann@x.com	Ann M Lee	2024-02-01 08:30:00
4	cara@x.com	Cara Kim	2024-01-05 12:00:00
5	bob@x.com	Robert Ray	2024-03-02 14:15:00
6	ann@x.com	Ann Lee	2024-01-20 07:45:00

Task

Return one row per email: the one with the latest updated_at. Break ties on updated_at by keeping the highest id. Output columns id, email, full_name, updated_at, ordered by email ascending.

Expected output

id	email	full_name	updated_at
3	ann@x.com	Ann M Lee	2024-02-01 08:30:00
5	bob@x.com	Robert Ray	2024-03-02 14:15:00
4	cara@x.com	Cara Kim	2024-01-05 12:00:00

Approach

Assign a ROW_NUMBER within each email group, ordered so the row you want to keep is number 1. Ordering by updated_at DESC puts the newest first, and adding id DESC makes the tie-break deterministic. Because a window function cannot appear in WHERE, compute the number in a CTE (or subquery) and filter rn = 1 in the outer query.

Query

WITH ranked AS (
    SELECT
        id,
        email,
        full_name,
        updated_at,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY updated_at DESC, id DESC
        ) AS rn
    FROM contacts
)
SELECT id, email, full_name, updated_at
FROM ranked
WHERE rn = 1
ORDER BY email;

Walkthrough

Partition ann@x.com holds rows 1 (Jan 10), 6 (Jan 20), 3 (Feb 1). Ordered by updated_at DESC, row 3 is rn = 1, so Ann M Lee survives.
Partition bob@x.com holds rows 2 (Jan 11) and 5 (Mar 2). Row 5 is newest, so Robert Ray survives.
Partition cara@x.com has only row 4, which is trivially rn = 1.
The outer query keeps the three rn = 1 rows and orders them by email, matching the expected output.

Complexity & notes

The engine sorts within each partition; overall cost is dominated by that sort, roughly O(n log n). A composite index on (email, updated_at DESC, id DESC) lets the planner stream rows in partition/order and avoid a separate sort.
If two rows shared the same email and identical updated_at with no further tie-break, ROW_NUMBER would still pick one, but which one would be non-deterministic — always include a unique column (here id) in ORDER BY.
To physically delete duplicates instead of selecting survivors, wrap the same CTE and run DELETE FROM contacts WHERE id IN (SELECT id FROM ranked WHERE rn > 1).
Use ROW_NUMBER (not RANK) for dedup: RANK would assign 1 to every tied row and let duplicates through.