SQL: Deduplicate Rows with `ROW_NUMBER()` | Local Agentic AI & Software

💻 Daily Code Snippet — August 7, 2026

SQL: Deduplicate Rows with ROW_NUMBER()

Works on PostgreSQL, MySQL 8+, SQL Server, SQLite 3.25+, Oracle.

Dry-run first — see what you'll delete:

SELECT *,
  ROW_NUMBER() OVER (
    PARTITION BY email
    ORDER BY created_at DESC, id ASC
  ) AS rn
FROM users;
-- rn=1 = keeper, rn>1 = duplicate

Delete the duplicates:

DELETE FROM users
WHERE id IN (
  SELECT id FROM (
    SELECT id,
      ROW_NUMBER() OVER (
        PARTITION BY email
        ORDER BY created_at DESC, id ASC
      ) AS rn
    FROM users
  ) ranked WHERE rn > 1
);

For massive tables — batch delete:

DELETE FROM users
WHERE id IN (
  SELECT id FROM (
    SELECT id,
      ROW_NUMBER() OVER (
        PARTITION BY email
        ORDER BY created_at DESC, id ASC
      ) AS rn
    FROM users
  ) ranked WHERE rn > 1
) LIMIT 1000;
-- Run until 0 rows affected

💡 Pro tips:

Use compound keys: PARTITION BY email, LOWER(name) for stricter matching

Pre-delete audit: SELECT SUM(CASE WHEN rn > 1 THEN 1 ELSE 0 END) to count dups first

This pattern keeps the newest row (ORDER BY created_at DESC) and the lowest ID as tiebreaker

← The Merge Conflict That Taught You Something You Fixed One Bug and Broke Three Others →