SQL Window Functions: Deduplication, Gaps and Islands, and Sessionization/Delete duplicates after ranking
Keep the newest record and remove the rest.
Section: Deduplication patterns
Delete duplicates after ranking
sql
sql
WITH ranked AS (
SELECT id,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY updated_at DESC, id DESC
) AS rn
FROM users
)
DELETE FROM users
WHERE id IN (
SELECT id FROM ranked WHERE rn > 1
);Explanation
Exact delete syntax can vary by database, but the ranking step is the core idea.
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
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.