SQL · Intermediate

Remove duplicate rows in SQL with ROW_NUMBER

14 min read

Why it matters

Duplicates are the most common data quality bug in analytics: a pipeline retries a load, an export is appended twice, a CRM holds the same customer under two ids, and every total after that is wrong. Knowing how to remove duplicates in SQL is daily work for analysts and data engineers (SQL is in 88% of Data Analyst and 80% of Data Engineer job descriptions we studied), and "delete duplicate emails keeping the smallest id" is a classic interview question. The harder skill, and the one this lesson spends most time on, is deciding whether a repeated row is a mistake at all.

The concept

Exact duplicates and business-key duplicates

Exact duplicates: every column is identical. Usually a load or retry bug, sometimes a real repeat (the same item scanned twice).
Business-key duplicates: the columns that should identify a row (customer id, email, invoice plus product) repeat, while other columns differ. Two rows for customer 12417 with different countries is a business-key duplicate.

You have to say which key defines "the same thing" before you can remove anything.

Finding them

sql

-- exact duplicates
SELECT col1, col2, ..., COUNT(*) AS copies
FROM t
GROUP BY col1, col2, ...
HAVING COUNT(*) > 1;

-- duplicates on a business key
SELECT key_col, COUNT(*) FROM t GROUP BY key_col HAVING COUNT(*) > 1;

Count both the number of duplicate groups and the number of extra rows. "5,268 extra rows in 4,879 groups" is a sentence a reviewer can check.

Errors or real repeats?

Before deleting, ask what produced the rows:

Looks like	Probably	Action
Same row, same load batch, seconds apart in an event log	Retry or double load	Keep one
Same item, quantity 1, many times on one receipt	Repeat scans at a till	Sum them, do not drop them
Same customer, different addresses	Customer moved, or two people	Keep the latest, or keep history
Same email, different case or spaces	One person	Normalise, then keep one

The wrong choice either leaves inflated totals or quietly deletes real sales.

Keeping one row per key with ROW_NUMBER

sql

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY business_key
                              ORDER BY updated_at DESC, id DESC) AS rn
    FROM t
)
SELECT * FROM ranked WHERE rn = 1;

The ORDER BY is the business rule: latest, most complete (ORDER BY phone IS NULL, ...), or first seen. Always end it with a unique tie-breaker, or which row survives can change between runs (see SQL ROW_NUMBER, RANK and DENSE_RANK: top N per group).

PostgreSQL and DuckDB have a shortcut, DISTINCT ON, that keeps the first row per key in the ORDER BY:

sql

SELECT DISTINCT ON (business_key) *
FROM t
ORDER BY business_key, updated_at DESC, id DESC;

Deleting duplicates in place

A table of exact duplicates has nothing to tell the copies apart, so you need a hidden row id or a rebuild:

PostgreSQL (ctid)

sql

DELETE FROM t
USING (
    SELECT ctid, ROW_NUMBER() OVER (PARTITION BY col1, col2, col3 ORDER BY ctid) AS rn
    FROM t
) AS d
WHERE t.ctid = d.ctid AND d.rn > 1;

SQL Server (CTE)

sql

WITH d AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2, col3 ORDER BY (SELECT NULL)) AS rn
    FROM t
)
DELETE FROM d WHERE rn > 1;

MySQL (with an id)

sql

DELETE t1
FROM t AS t1
JOIN t AS t2 ON t2.key_col = t1.key_col AND t2.id < t1.id;

ctid is PostgreSQL's physical row address. It is fine inside one statement, but it changes when rows are updated or the table is vacuumed, so never store it.

The safer route on big or important tables is rebuild and swap: create a new deduplicated table, check its counts and totals, then rename the tables inside a transaction. The old table stays as a backup until you drop it.

Preventing them next time

Add a UNIQUE constraint (or primary key) on the business key once it is clean. Then a duplicate load fails loudly instead of silently doubling totals.
Load with an upsert (INSERT ... ON CONFLICT in PostgreSQL, MERGE elsewhere) so a retry updates instead of inserting again. Upserts get their own lesson in the relational design module.
In warehouses that do not enforce keys, add a uniqueness test to the pipeline.

Logging what you removed

Every dedup step should leave a record: rows before, rows after, and the rule. When someone asks in three months why revenue changed, the log answers.

Worked example: duplicates in a retail export

The data is the UCI Online Retail export (541,909 lines from a UK gift shop, CC BY 4.0), loaded into PostgreSQL 16 as-is. Every output is from a real run.

Setup

Download the CSV from the Spark Definitive Guide repository, create an empty database, and run in psql from the folder with the file:

sql

CREATE TABLE raw_retail (
    invoice_no text, stock_code text, description text, quantity text,
    invoice_date text, unit_price text, customer_id text, country text
);
\copy raw_retail FROM 'online-retail-dataset.csv' WITH (FORMAT csv, HEADER true)

CREATE TABLE lines AS
SELECT invoice_no, stock_code, description,
       quantity::integer AS quantity,
       to_timestamp(invoice_date, 'MM/DD/YYYY HH24:MI')::timestamp AS invoiced_at,
       unit_price::numeric(10,3) AS unit_price,
       customer_id::integer AS customer_id,
       country
FROM raw_retail;

Step 1: count exact duplicates

sql

SELECT count(*) AS all_rows,
       count(*) - (SELECT count(*) FROM (SELECT DISTINCT * FROM lines) d) AS exact_duplicate_rows
FROM lines;

SELECT invoice_no, stock_code, description, quantity, invoiced_at, unit_price, count(*) AS copies
FROM lines
GROUP BY invoice_no, stock_code, description, quantity, invoiced_at, unit_price, customer_id, country
HAVING count(*) > 1
ORDER BY copies DESC, invoice_no, stock_code
LIMIT 5;

Output

 all_rows | exact_duplicate_rows
----------+----------------------
   541909 |                 5268
 
 invoice_no | stock_code |             description             | quantity |     invoiced_at     | unit_price | copies
------------+------------+-------------------------------------+----------+---------------------+------------+--------
 555524     | 22698      | PINK REGENCY TEACUP AND SAUCER      |        1 | 2011-06-05 11:37:00 |      2.950 |     20
 555524     | 22697      | GREEN REGENCY TEACUP AND SAUCER     |        1 | 2011-06-05 11:37:00 |      2.950 |     12
 572861     | 22775      | PURPLE DRAWERKNOB ACRYLIC EDWARDIAN |       12 | 2011-10-26 12:46:00 |      1.250 |      8
 538514     | 21756      | BATH BUILDING BLOCK WORD            |        1 | 2010-12-12 14:27:00 |      5.950 |      6
 540524     | 21756      | BATH BUILDING BLOCK WORD            |        1 | 2011-01-09 12:53:00 |      5.950 |      6

5,268 rows are exact copies of another row. The GROUP BY lists every column, which is what "exact" means. The top of the list already tells a story: 20 identical lines of one pink teacup on one invoice.

Step 2: are they errors?

Look at invoice 555524 as a whole:

sql

SELECT stock_code, count(*), sum(quantity)
FROM lines
WHERE invoice_no = '555524'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 4;

Output

 stock_code | count | sum
------------+-------+-----
 22698      |    20 |  20
 22697      |    12 |  12
 22699      |     3 |  12
 21155      |     2 |   2

The rose teacup (22699) appears on 3 lines that add up to 12 cups, so they are not identical, yet they are clearly the same product bought in several goes. The pink teacup's 20 lines of quantity 1 fit the same pattern: someone scanning cups one at a time. These are real sales recorded line by line, not a double load.

And business-key repeats (same invoice and product) are far more common than exact copies:

sql

SELECT count(*) AS key_groups_with_repeats,
       sum(n) AS rows_in_them,
       count(*) FILTER (WHERE distinct_qty > 1) AS groups_with_different_quantities
FROM (
    SELECT invoice_no, stock_code, count(*) AS n, count(DISTINCT quantity) AS distinct_qty
    FROM lines
    GROUP BY invoice_no, stock_code
    HAVING count(*) > 1
) g;

Output

 key_groups_with_repeats | rows_in_them | groups_with_different_quantities
-------------------------+--------------+----------------------------------
                    9694 |        20378 |                             5084

In 5,084 of the 9,694 groups the quantities differ, so "keep one row" would be plainly wrong there. What does a blind DISTINCT cost?

sql

SELECT round(sum(quantity * unit_price), 2) AS revenue_all_rows,
       (SELECT round(sum(quantity * unit_price), 2)
        FROM (SELECT DISTINCT * FROM lines) d) AS revenue_after_distinct
FROM lines;

Output

 revenue_all_rows | revenue_after_distinct
------------------+------------------------
       9747747.93 |             9726006.95

£21,740.98 of sales would disappear. The right fix here is not to delete rows but to merge them: one row per invoice, product and price, with quantities summed.

Step 3: merge to one row per business key, with an audit count

sql

BEGIN;
CREATE TABLE sales_lines_new AS
SELECT invoice_no, stock_code,
       max(description) AS description,
       sum(quantity)    AS quantity,
       unit_price,
       min(invoiced_at) AS invoiced_at,
       customer_id, country,
       count(*)         AS source_rows
FROM lines
GROUP BY invoice_no, stock_code, unit_price, customer_id, country;

ALTER TABLE sales_lines_new
    ADD CONSTRAINT sales_lines_key UNIQUE (invoice_no, stock_code, unit_price);

SELECT count(*) AS rows_after,
       sum(source_rows) AS source_rows,
       sum(source_rows) - count(*) AS rows_merged,
       round(sum(quantity * unit_price), 2) AS revenue
FROM sales_lines_new;

SELECT invoice_no, stock_code, description, quantity, unit_price, source_rows
FROM sales_lines_new
WHERE invoice_no = '555524' AND stock_code IN ('22698', '22697', '22699')
ORDER BY stock_code;
COMMIT;

Output

BEGIN
SELECT 531801
ALTER TABLE
 rows_after | source_rows | rows_merged |  revenue
------------+-------------+-------------+------------
     531801 |      541909 |       10108 | 9747747.93
 
 invoice_no | stock_code |           description            | quantity | unit_price | source_rows
------------+------------+----------------------------------+----------+------------+-------------
 555524     | 22697      | GREEN REGENCY TEACUP AND SAUCER  |       12 |      2.950 |          12
 555524     | 22698      | PINK REGENCY TEACUP AND SAUCER   |       20 |      2.950 |          20
 555524     | 22699      | ROSES REGENCY TEACUP AND SAUCER  |       12 |      2.950 |           3
COMMIT

What this proves:

Revenue is unchanged (9,747,747.93), so no sale was lost. That check is the whole point.
source_rows keeps the audit trail on every row: the pink teacup line came from 20 scans.
unit_price is part of the key on purpose, so the same product sold at two prices on one invoice stays as two rows.
The UNIQUE constraint was added successfully, which proves the key really is unique now, and it will block a future double load.

Step 4: log it and swap

sql

CREATE TABLE dedup_log (
    run_at      timestamptz NOT NULL DEFAULT now(),
    table_name  text        NOT NULL,
    rows_before bigint      NOT NULL,
    rows_after  bigint      NOT NULL,
    rule        text        NOT NULL
);

INSERT INTO dedup_log (table_name, rows_before, rows_after, rule)
SELECT 'sales_lines',
       (SELECT count(*) FROM lines),
       (SELECT count(*) FROM sales_lines_new),
       'one row per invoice_no, stock_code, unit_price; quantity summed; source_rows kept';

SELECT table_name, rows_before, rows_after, rows_before - rows_after AS removed, rule FROM dedup_log;

ALTER TABLE lines RENAME TO lines_raw;
ALTER TABLE sales_lines_new RENAME TO sales_lines;

INSERT INTO sales_lines (invoice_no, stock_code, description, quantity, unit_price,
                         invoiced_at, customer_id, country, source_rows)
VALUES ('555524', '22698', 'PINK REGENCY TEACUP AND SAUCER', 1, 2.95,
        '2011-06-05 11:37', NULL, 'United Kingdom', 1);

Output

 table_name  | rows_before | rows_after | removed |                                       rule
-------------+-------------+------------+---------+-----------------------------------------------------------------------------------
 sales_lines |      541909 |     531801 |   10108 | one row per invoice_no, stock_code, unit_price; quantity summed; source_rows kept
 
ALTER TABLE
ALTER TABLE
ERROR:  duplicate key value violates unique constraint "sales_lines_key"
DETAIL:  Key (invoice_no, stock_code, unit_price)=(555524, 22698, 2.950) already exists.

The swap is two renames, and the raw table stays as lines_raw until you are sure. The last insert simulates a pipeline loading the same line again: the constraint now rejects it.

Step 5: deleting exact copies in place, when that is the right call

If you have established that exact copies are load errors (for example in an event log with a retry bug), you can delete them in place. Rehearsed inside a transaction on a copy of the raw table:

sql

CREATE TABLE lines_copy AS SELECT * FROM lines_raw;
BEGIN;
DELETE FROM lines_copy AS l
USING (
    SELECT ctid,
           ROW_NUMBER() OVER (PARTITION BY invoice_no, stock_code, description, quantity,
                                           invoiced_at, unit_price, customer_id, country
                              ORDER BY ctid) AS rn
    FROM lines_copy
) AS d
WHERE l.ctid = d.ctid
  AND d.rn > 1;
SELECT count(*) AS rows_left FROM lines_copy;
ROLLBACK;

Output

SELECT 541909
BEGIN
DELETE 5268
 rows_left
-----------
    536641
ROLLBACK

DELETE 5268 matches the count from Step 1 exactly, which is the check that the rule did what you meant. Here we rolled back, because for this shop those rows are real sales.

Step 6: one row per customer, keeping the latest

A different kind of duplicate: eight customers appear with two countries. For a customer table we want one row each, with the most recent country:

sql

SELECT customer_id, country, min(invoiced_at) AS first_seen, max(invoiced_at) AS last_seen
FROM lines_raw
WHERE customer_id IN (12370, 12394, 12417)
GROUP BY 1, 2
ORDER BY 1, 3;

WITH ranked AS (
    SELECT customer_id, country, invoiced_at, invoice_no,
           ROW_NUMBER() OVER (PARTITION BY customer_id
                              ORDER BY invoiced_at DESC, invoice_no DESC) AS rn
    FROM lines_raw
    WHERE customer_id IS NOT NULL
)
SELECT customer_id, country, invoiced_at AS last_seen
FROM ranked
WHERE rn = 1 AND customer_id IN (12370, 12394, 12417)
ORDER BY customer_id;

SELECT DISTINCT ON (customer_id) customer_id, country, invoiced_at AS last_seen
FROM lines_raw
WHERE customer_id IN (12370, 12394, 12417)
ORDER BY customer_id, invoiced_at DESC, invoice_no DESC;

Output

 customer_id | country |     first_seen      |      last_seen
-------------+---------+---------------------+---------------------
       12370 | Cyprus  | 2010-12-14 12:58:00 | 2011-10-19 14:51:00
       12370 | Austria | 2010-12-17 09:38:00 | 2010-12-17 09:38:00
       12394 | Belgium | 2011-05-06 14:01:00 | 2011-05-06 14:01:00
       12394 | Denmark | 2011-10-07 08:08:00 | 2011-10-07 08:08:00
       12417 | Belgium | 2010-12-17 11:51:00 | 2011-12-06 14:52:00
       12417 | Spain   | 2011-04-28 13:09:00 | 2011-04-28 13:09:00
 
 customer_id | country |      last_seen
-------------+---------+---------------------
       12370 | Cyprus  | 2011-10-19 14:51:00
       12394 | Denmark | 2011-10-07 08:08:00
       12417 | Belgium | 2011-12-06 14:52:00
 
 customer_id | country |      last_seen
-------------+---------+---------------------
       12370 | Cyprus  | 2011-10-19 14:51:00
       12394 | Denmark | 2011-10-07 08:08:00
       12417 | Belgium | 2011-12-06 14:52:00

ROW_NUMBER and DISTINCT ON give the same answer. Customer 12370's single Austrian invoice looks like a one-off delivery address, and the rule "latest wins" correctly keeps Cyprus. If the business needed address history, you would keep all rows with valid-from and valid-to dates instead (a slowly changing dimension), not deduplicate.

Common mistakes

Running DISTINCT on everything. It removes real repeat purchases along with errors. Here it would have deleted £21,740.98 of sales.
Deduplicating without defining the key. "Duplicate" only means something relative to a key. Write the key down first.
ROW_NUMBER with no tie-breaker. The surviving row can change between runs. End the ORDER BY with a unique column.
Not checking totals after. Row counts tell you how many rows went; a revenue or quantity total tells you whether anything real went with them.
Deleting in place on a production table without a rehearsal. Run it inside BEGIN ... ROLLBACK first and compare the delete count with your duplicate count, or rebuild and swap.
Storing ctid. It changes after updates and VACUUM. Use it only within one statement.
Cleaning once and not adding a constraint. The next retry brings the duplicates back. Add UNIQUE or load with an upsert.
Treating case and spaces as different values. Asha@Example.com and asha@example.com are the same person. Normalise with lower(trim(...)) before you compare.

Interview angle

Delete duplicate emails from a users table, keeping the row with the smallest id.

sql

DELETE FROM users AS u
USING users AS keep
WHERE lower(u.email) = lower(keep.email)
  AND u.id > keep.id;

Every row that has another row with the same email and a smaller id is deleted, so only the smallest id per email remains. In MySQL: DELETE u FROM users u JOIN users keep ON lower(keep.email) = lower(u.email) AND keep.id < u.id. Tested on six rows with ravi@example.com three times and Asha@Example.com once next to asha@example.com, it deleted ids 3, 4 and 6 and kept 1, 2 and 5. Afterwards I would add CREATE UNIQUE INDEX ON users (lower(email)) so it cannot happen again, and I would run the delete inside a transaction and check the count first.

How do you find duplicates in a table?

GROUP BY the columns that define "the same" and keep groups with HAVING COUNT(*) > 1. For exact duplicates group by every column; for business duplicates group by the key. Report both the number of groups and the number of extra rows, then look at a few groups before deciding what to do.

How do you tell real repeat purchases from duplicate rows?

Look at how the rows were produced: identical event ids or load batch ids and timestamps seconds apart point to retries; repeated quantity-1 lines on one receipt point to repeat scans. Compare against a total from the source system if one exists. When the repeats are real, merge them by summing quantities instead of deleting, and check that the revenue total is unchanged.

Practice task

Build a clean customer_dim table from lines_raw (the original lines):

One row per customer with the current country (from the latest invoice, ties broken by invoice number), first seen, last seen, the number of countries seen, and the number of source rows.
Make customer_id the primary key.
Prove nothing was lost: the number of customers must equal the distinct customer count, and the sum of source rows must equal the number of raw lines that have a customer id.
List the customers who changed country.

Done when the table has a primary key, 4,372 rows, and its source rows add up to the raw line count.

Show a solution

sql

CREATE TABLE customer_dim AS
WITH ranked AS (
    SELECT customer_id, country, invoiced_at,
           ROW_NUMBER() OVER (PARTITION BY customer_id
                              ORDER BY invoiced_at DESC, invoice_no DESC) AS rn
    FROM lines_raw
    WHERE customer_id IS NOT NULL
),
stats AS (
    SELECT customer_id,
           min(invoiced_at) AS first_seen,
           count(DISTINCT country) AS countries_seen,
           count(*) AS source_rows
    FROM lines_raw
    WHERE customer_id IS NOT NULL
    GROUP BY customer_id
)
SELECT r.customer_id, r.country AS current_country, s.first_seen,
       r.invoiced_at AS last_seen, s.countries_seen, s.source_rows
FROM ranked AS r
JOIN stats AS s ON s.customer_id = r.customer_id
WHERE r.rn = 1;

ALTER TABLE customer_dim ADD PRIMARY KEY (customer_id);

SELECT count(*) AS customers,
       count(*) FILTER (WHERE countries_seen > 1) AS moved_country,
       sum(source_rows) AS source_rows
FROM customer_dim;

SELECT customer_id, current_country, countries_seen
FROM customer_dim WHERE countries_seen > 1 ORDER BY customer_id LIMIT 3;

SELECT count(*) AS lines_with_customer FROM lines_raw WHERE customer_id IS NOT NULL;

Output

SELECT 4372
ALTER TABLE
 customers | moved_country | source_rows
-----------+---------------+-------------
      4372 |             8 |      406829
 
 customer_id | current_country | countries_seen
-------------+-----------------+----------------
       12370 | Cyprus          |              2
       12394 | Denmark         |              2
       12417 | Belgium         |              2
 
 lines_with_customer
---------------------
              406829

4,372 rows with a primary key that was accepted, 406,829 source rows matching the raw lines with a customer id, and 8 customers flagged as having moved. The source_rows and countries_seen columns are the audit: anyone can see what each clean row was built from.

#Why it matters

#The concept

#Exact duplicates and business-key duplicates

#Finding them

#Errors or real repeats?

#Keeping one row per key with ROW_NUMBER

#Deleting duplicates in place

#Preventing them next time

#Logging what you removed

#Worked example: duplicates in a retail export

#Step 1: count exact duplicates

#Step 2: are they errors?

#Step 3: merge to one row per business key, with an audit count

#Step 4: log it and swap

#Step 5: deleting exact copies in place, when that is the right call

#Step 6: one row per customer, keeping the latest

#Common mistakes

#Interview angle

#Practice task

#Further reading

Why it matters

The concept

Exact duplicates and business-key duplicates

Finding them

Errors or real repeats?

Keeping one row per key with ROW_NUMBER

Deleting duplicates in place

Preventing them next time

Logging what you removed

Worked example: duplicates in a retail export

Step 1: count exact duplicates

Step 2: are they errors?

Step 3: merge to one row per business key, with an audit count

Step 4: log it and swap

Step 5: deleting exact copies in place, when that is the right call

Step 6: one row per customer, keeping the latest

Common mistakes

Interview angle

Practice task

Further reading