Forgly

SQL to delete duplicate rows (keep one)

Remove duplicate rows while keeping a single copy of each.

PostgreSQL, SQL Server, SQLite 3.25+ (window function)
WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (
           PARTITION BY email ORDER BY id
         ) AS rn
  FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);
MySQL 8+
DELETE u FROM users u
JOIN users keep
  ON keep.email = u.email
 AND keep.id < u.id;

Joins each row to any earlier row with the same email and deletes the later ones, keeping the smallest id.

PostgreSQL (self-join alternative)
DELETE FROM users a
USING users b
WHERE a.id > b.id
  AND a.email = b.email;

How to delete duplicate rows but keep one

Deleting duplicates means keeping one row per group (usually the lowest id) and removing the rest. The cleanest cross-dialect approach uses ROW_NUMBER().

How it works

  • ROW_NUMBER() numbers rows 1, 2, 3… within each PARTITION BY group, ordered so the row you keep gets number 1.
  • Every row with rn > 1 is a duplicate to remove.
  • ORDER BY id inside the window decides which copy survives — order by created_at to keep the newest, for example.

Tip

Always run the SELECT version first to preview which rows would be deleted, then wrap the delete in a transaction.

Related SQL queries

Frequently asked questions

How do I delete duplicate rows but keep one in SQL?

Deleting duplicates means keeping one row per group (usually the lowest id) and removing the rest. The cleanest cross-dialect approach uses ROW_NUMBER(). ROW_NUMBER() numbers rows 1, 2, 3… within each PARTITION BY group, ordered so the row you keep gets number 1. Every row with rn > 1 is a duplicate to remove. ORDER BY id inside the window decides which copy survives — order by created_at to keep the newest, for example.

Does this work in PostgreSQL, MySQL, SQL Server, and SQLite?

Yes — this page lists the query for each dialect, since the syntax can differ between database engines.

Can I generate this query for my own tables?

Yes. Describe what you want in plain English with Forgly's free AI SQL Generator and it writes the query for your dialect.