SQL Window Functions: Deduplication, Gaps and Islands, and Sessionization/Deduplicate and keep the latest row
Choose one row per business key.
Section: Deduplication patterns
Deduplicate and keep the latest row
sql
sql
WITH ranked AS (
SELECT t.*,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY updated_at DESC, id DESC
) AS rn
FROM users t
)
SELECT *
FROM ranked
WHERE rn = 1;Explanation
This pattern is safer and clearer than many self-join alternatives.
Learn the surrounding workflow
Compare similar commands or jump into common fixes when this command is part of a bigger troubleshooting path.
Related commands
Same sheet · prioritizing Deduplication patterns
Delete duplicates after ranking
Keep the newest record and remove the rest.
Flag a new session when the gap is too large
Start a new session after 30 minutes of inactivity.
Assign session IDs with a running sum
Convert session boundary flags into stable groups.
Group consecutive dates into streaks
Classic islands pattern using row-number arithmetic.