SQL: Find Duplicate Rows — Detect, Count, Delete

🔧 Code Snippet — August 12, 2026

SQL: Find Duplicate Rows — Detect, Count, Delete

Failed import? Race condition? Double-submissions? The ROW_NUMBER() window function is your Swiss Army knife for deduplication.

-- STEP 1: Find what's duplicated (audit)
SELECT email, COUNT(*) AS dup_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY dup_count DESC;

-- STEP 2: Rank duplicates — keep the oldest
SELECT id, email, created_at,
       ROW_NUMBER() OVER (
           PARTITION BY email
           ORDER BY created_at ASC, id ASC
       ) AS row_num
FROM users;

-- STEP 3: Preview what you'll delete
WITH ranked AS (
    SELECT id, email,
           ROW_NUMBER() OVER (
               PARTITION BY email
               ORDER BY created_at ASC, id ASC
           ) AS row_num
    FROM users
)
SELECT * FROM ranked WHERE row_num > 1;

-- STEP 4: Delete duplicates (keep oldest)
DELETE FROM users WHERE id IN (
    SELECT id FROM (
        SELECT id, ROW_NUMBER() OVER (
            PARTITION BY email ORDER BY created_at ASC, id ASC
        ) AS row_num FROM users
    ) ranked WHERE row_num > 1
);

Variations:

• Multi-column: PARTITION BY col1, col2, col3


• Case-insensitive: PARTITION BY LOWER(email)


• Trim + lowercase: PARTITION BY LOWER(TRIM(username))


• Fuzzy match (PG): pg_trgm extension for Levenshtein

💡 Bookmark this. Works on PostgreSQL, MySQL 8+, SQLite 3.25+, SQL Server, Snowflake, BigQuery. Always run Step 3 before Step 4, and wrap in a transaction.