Deduplicate rows

The problem

A table has rows that should be unique on some key but aren't — a double-submitted order, a botched import, a bad join upstream. You want one row per key.

Look before you delete

orders 106 and 107 are the same customer, date, and amount — a duplicate submission. Find the dupes first:

Loading editor…

The pattern

Number rows within each key group, keep rn = 1:

Loading editor…

The ORDER BY inside the window decides which copy you keep — here the lowest order_id. Pick deliberately (earliest created_at, non-null-est row, etc.).

Actually deleting them

-- Postgres / SQLite: delete by the rows you want to keep
DELETE FROM orders
WHERE order_id NOT IN (
  SELECT MIN(order_id)
  FROM orders
  GROUP BY customer_id, order_date, amount
);

-- Engines with deletable CTEs (Postgres, SQL Server)
WITH ranked AS (
  SELECT ctid, ROW_NUMBER() OVER (
    PARTITION BY customer_id, order_date, amount ORDER BY order_id
  ) AS rn
  FROM orders
)
DELETE FROM orders WHERE ctid IN (SELECT ctid FROM ranked WHERE rn > 1);

Gotchas

• Define "duplicate" precisely. Rows that differ only in an updated_at or a surrogate id aren't duplicates for every purpose. Partition by the columns that actually define identity.
• SELECT DISTINCT only helps when the entire row is identical. It can't keep "one row per customer" while returning other columns.
• Run the SELECT version first, eyeball the count, then convert to DELETE.