Deduplicate a table keeping one row per user — the latest by updated_at
Table users_raw(user_id, updated_at, email, plan) has several rows per user from repeated syncs. Return exactly one row per user_id — the most recent by updated_at — keeping all of that row's columns.
-- keep one row per user_id: the latest by updated_atWrite the query.
Rank rows within each user and keep the top one: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn, then filter rn = 1. DISTINCT cannot pick the latest; a plain GROUP BY user_id forces an aggregate on every other column.
- ✗Expecting DISTINCT to pick the latest row per user
- ✗Assuming MAX() drags the other columns from the same row
- ✗Grouping by user_id but selecting ungrouped columns
- →How would you break ties on equal updated_at values?
- →When is DISTINCT ON simpler than ROW_NUMBER here?
Number the rows within each user newest-first, then keep rank one. This returns the whole original row, not just the aggregated key.
WITH ranked AS (
SELECT users_raw.*,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn
FROM users_raw
)
SELECT user_id, updated_at, email, plan
FROM ranked
WHERE rn = 1;In Postgres, DISTINCT ON is a shorter equivalent:
SELECT DISTINCT ON (user_id) user_id, updated_at, email, plan
FROM users_raw
ORDER BY user_id, updated_at DESC;Why the alternatives fail: DISTINCT only removes rows that are identical across every selected column, so differing updated_at/email values keep the duplicates. GROUP BY user_id collapses each user to one row but then every other column must be wrapped in an aggregate — and MAX(updated_at) with a bare email does not guarantee they come from the same row.
