💻
Code Snippet — Aug 12, 2026SQL: Find Duplicate Rows — Detect, Count, and Decide What to KeepFailed import, race condition, or double-submission? The naive
GROUP BY ... HAVING COUNT() > 1 tells you what's
duplicated but not which rows
to keep. ROW_NUMBER() gives you the complete dedup toolkit:
-- Preview rows that will be deleted (keep oldest per email)
WITH ranked AS (
SELECT id, email, created_at,
ROW_NUMBER() OVER (
PARTITION BY LOWER(email)
ORDER BY created_at ASC, id ASC
) AS row_num
FROM users
)
SELECT * FROM ranked WHERE row_num > 1;
-- Then delete them
DELETE FROM users
WHERE id IN (SELECT id FROM ranked WHERE row_num > 1);
The pattern:
PARTITION BY — defines what makes two rows "the same"
ORDER BY inside window — picks the winner (oldest, newest, highest ID)
row_num = 1 — always the row you keep
WHERE row_num > 1 — everything else is a duplicate
Multi-column dedup: PARTITION BY product_id, warehouse_id, log_date
Case-insensitive: PARTITION BY LOWER(email)
Fuzzy (PostgreSQL): Add pg_trgm extension for Levenshtein matching
💡 Works everywhere — PostgreSQL, MySQL 8+, SQLite 3.25+, SQL Server, Snowflake, BigQuery. Always wrap deletes in a transaction.*