Duplicates

Rows are only duplicates with respect to a key you choose, so choose it first, then decide which copy deserves to survive.

The idea

Nothing in a table is inherently a duplicate. Two rows become duplicates the moment you declare which columns have to match: that set of columns is the duplicate key.

Once the key is named, the rest is mechanical: group the rows by that key, keep the stacks with more than one row, order each stack so the copy you want is first, number the copies, and delete everything numbered above one. DISTINCT is a different tool entirely: it collapses repeats in one result set and leaves the stored rows untouched.

See it happen

duplicate key

tiebreak inside a stack

step 0 / 4 key (email) stacks 3 stacks with copies 2 rows removed 0 keeps -

the clause doing the work

How it works

Three separate jobs, in order. Naming the key is a decision about the data, not SQL. Counting tells you whether a problem exists. Numbering is what lets you keep exactly one copy, because ROW_NUMBER() restarts at 1 inside every partition and its ORDER BY decides which row gets the 1.

-- 0. the decision: one signup per email address.
--    change this key and "duplicate" means something else.

-- 1. who are the offenders, and how bad is it?
SELECT email, COUNT(*) AS copies
FROM signups
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY copies DESC;

-- 2. number the copies inside each key, best copy first
WITH numbered AS (
  SELECT id,
         ROW_NUMBER() OVER (
           PARTITION BY email                 -- the duplicate key
           ORDER BY created_at DESC, id DESC   -- newest wins; id breaks ties
         ) AS rn
  FROM signups
)
-- 3. rn = 1 is the keeper, everything else goes
DELETE FROM signups
WHERE id IN (SELECT id FROM numbered WHERE rn > 1);

-- 4. make the mess impossible from now on
CREATE UNIQUE INDEX signups_email_uniq ON signups (email);

-- DISTINCT is not part of the fix; it only tidies one result:
SELECT DISTINCT email FROM signups;   -- table unchanged

Cost

step	time	space
GROUP BY key + HAVING	O(n) with a hash aggregate; O(n log n) if the planner sorts	O(k), one entry per distinct key
ROW_NUMBER() OVER (PARTITION BY key ORDER BY …)	O(n log n): a sort to produce the partition order, unless an index already supplies it	O(n) worst case for the sort / window buffer
DELETE of d marked rows	O(d log n) for index and constraint upkeep	O(d) for the list of ids
SELECT DISTINCT	O(n) hash, or O(n log n) sort	O(k) for the distinct output

The trade-off: GROUP BY tells you how many copies exist and is cheap. ROW_NUMBER is the only one that tells you which copy to keep, and you pay a sort for that judgement. DISTINCT is the cheapest and the least useful: it changes what you see, not what is stored.

Watch out for

Ties in the tiebreak. If two copies share the same created_at, ROW_NUMBER picks between them arbitrarily and may pick differently on the next run. Always append a unique, stable column: ORDER BY created_at DESC, id DESC.
NULLs behave two ways. GROUP BY email puts all NULL emails in one group, but a unique index treats each NULL as distinct in most engines, so a constraint will happily let NULL-key duplicates in. And a self-join written as a.email = b.email silently misses them, because NULL = NULL is never true.
The key is dirtier than it looks. 'Ana@X.co ' and 'ana@x.co' are different keys under a case-sensitive collation. If the business rule is "one per address", group on the normalised value, lower(trim(email)), and index the same expression.
DISTINCT fixes nothing. It applies to the whole select list (not just the column you cared about), it disappears the moment someone writes the query without it, and the extra rows are still there feeding every other report, join and count.
Deleting without a keeper condition. DELETE FROM signups WHERE email IN (…duplicated emails…) removes both copies. Run the SELECT first, delete only rn > 1, wrap it in a transaction, and add the unique index afterwards so it cannot recur. Also note COUNT(col) skips NULLs while COUNT(*) counts rows: use COUNT(*) here.

Worked example

Marketing reports that some people got the welcome email twice. The signups table has (id, email, name, created_at). You declare the key: one signup per email address. GROUP BY email HAVING COUNT(*) > 1 returns two rows, ana@x.co with 3 copies and bo@x.co with 2, so five stored rows are involved and three of them have to go.

Which three? The newest signup carries the most recent consent flag, so the tiebreak is ORDER BY created_at DESC, id DESC. ROW_NUMBER stamps #105 and #106 with 1, and #101, #103, #102 with 2 or 3; deleting rn > 1 leaves one row per address. Had the rule instead been "keep the original account", flipping to ASC would have saved #101 and #102 and deleted the newer three: same query shape, different survivors.

And notice what widening the key does: with the key (email, name), row #103 (ana@x.co, Ana R.) stops being a duplicate at all and survives. Nothing about the data changed: only the definition did. Finish by adding CREATE UNIQUE INDEX … ON signups (lower(email)), or the cleanup becomes a monthly chore.

Check yourself

A table holds (id, email, created_at). Two rows share an email but have different ids. Are they duplicates?

You wrap a report in SELECT DISTINCT and the repeats vanish from the output. What changed in the table?