Forgly

SQL to delete duplicate rows (keep one)

Delete duplicate rows in SQL while keeping one copy — remove duplicate records in PostgreSQL, MySQL, SQL Server, and SQLite.

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. To delete duplicate rows but keep one, the cleanest cross-dialect approach uses ROW_NUMBER(); MySQL can do it with a self-join.

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. To delete duplicate rows but keep one, the cleanest cross-dialect approach uses ROW_NUMBER(); MySQL can do it with a self-join. 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.