Find rows duplicated by (email, phone) and keep only the earliest one
Table people(id, email, phone, created_at) has duplicate people sharing the same (email, phone). Return exactly one row per (email, phone) — the earliest by created_at — so the duplicates collapse to their first occurrence.
-- one row per (email, phone): the earliest by created_atWrite the query.
Number the rows within each (email, phone) pair by age and keep the first — ROW_NUMBER() OVER (PARTITION BY email, phone ORDER BY created_at) AS rn in a subquery, then filter rn = 1. A plain GROUP BY would collapse the group but could not return the whole earliest row.
- ✗Expecting DISTINCT to pick the earliest row rather than exact duplicates
- ✗Assuming MIN(created_at) drags the other columns from the same row
- ✗Relying on physical row order instead of an explicit ORDER BY
- →How would you break ties when two rows share the same created_at?
- →How does this query change if you must delete the duplicates in place?
Number the rows within each (email, phone) pair oldest-first, then keep rank one. This returns the whole original row, not just the grouping key.
WITH ranked AS (
SELECT people.*,
ROW_NUMBER() OVER (PARTITION BY email, phone
ORDER BY created_at) AS rn
FROM people
)
SELECT id, email, phone, created_at
FROM ranked
WHERE rn = 1;Why the alternatives fail: DISTINCT only removes rows identical across every selected column, so differing id/created_at values keep the duplicates. GROUP BY email, phone with MIN(created_at) collapses each pair, but a bare id beside it is not guaranteed to come from that earliest row. The window function keeps the full row and picks the first deterministically.
