SQL Deduplication Patterns

Before removing duplicates, decide what a duplicate is. Two rows can be exact copies, equal in every column, usually because a load ran twice. Or they can share a key (the same customer, the same email) while other columns differ, and then you have to choose which row to keep. Each needs a different query.

The examples below use a copy of Garden Shop's customers where an import ran twice for three of them:

create or replace table customers_import as
select * from customers
union all
select * from customers where customer_id in (2, 5, 9);

select count(*)                    as rows,
       count(distinct customer_id) as customers
from customers_import

That's 23 rows for 20 customers.

Find them first

Group by the columns that should be unique and keep the groups with more than one row. This shows which keys repeat and how often, before you change anything:

select customer_id,
       count(*) as copies
from customers_import
group by customer_id
having count(*) > 1
order by customer_id

Customers 2, 5, and 9 have two rows each. To find exact copies, group by every column instead. Don't compare every column with = in a self-join: NULL = NULL isn't true, so two copies of a customer with no phone number never match. Customers 2, 5, and 9 have no phone, so a self-join on all nine columns finds none of the three copies. GROUP BY and DISTINCT treat NULLs as equal, which is what you want here.

Exact copies: DISTINCT

When the rows are identical, select distinct * keeps one of each, and it doesn't matter which:

select count(*) as rows
from (select distinct * from customers_import)

20 rows. DISTINCT only helps when every selected column matches. If the copies differ anywhere, even in a load timestamp, they're all distinct and nothing is removed.

Same key, different rows: keep one

Now suppose the second load brought newer email addresses, so the copies differ:

create or replace table customer_loads as
select customer_id, first_name, email,
       timestamp '2024-06-01 02:00:00' as loaded_at
from customers
union all
select customer_id, first_name,
       replace(email, '@example.com', '@mail.example.com'),
       timestamp '2024-07-01 02:00:00'
from customers
where customer_id in (2, 5, 9);

select count(*) as rows
from (select distinct customer_id, first_name, email from customer_loads)

DISTINCT still returns 23 rows. Number the rows within each key, in the order that says which one wins, and keep row 1:

with ranked as (
  select *,
         row_number() over (
           partition by customer_id
           order by loaded_at desc
         ) as rn
  from customer_loads
)
select customer_id, first_name, email, loaded_at
from ranked
where rn = 1
order by customer_id

20 rows, and customers 2, 5, and 9 keep their newer email. The PARTITION BY defines the duplicate group; the ORDER BY is the rule for which row survives. Leave the ORDER BY out and the survivor is an accident: deduplicating without ORDER BY keeps the wrong row. If two rows can tie on the order, add a unique column last, and order by the date rather than the id: the highest id isn't always the latest row.

Shorter forms

The same result, written three other ways:

-- QUALIFY filters on the window directly (DuckDB, Snowflake, BigQuery)
select *
from customer_loads
qualify row_number() over (partition by customer_id order by loaded_at desc) = 1;

-- DISTINCT ON keeps the first row per key in ORDER BY order (DuckDB, PostgreSQL)
select distinct on (customer_id) *
from customer_loads
order by customer_id, loaded_at desc;

-- arg_max picks one column's value from the latest row (DuckDB)
select customer_id,
       arg_max(email, loaded_at) as email
from customer_loads
group by customer_id

All three return 20 customers with the same emails. QUALIFY and DISTINCT ON keep whole rows; arg_max is shortest when you need one or two columns. See DISTINCT ON and the window function reference.

Delete duplicates in place

The queries above return clean rows; the table still has the copies. To remove them from the table itself, delete every row that isn't the first of its group. DuckDB gives every table row a hidden rowid to tell copies apart:

delete from customers_import
where rowid not in (
  select min(rowid)
  from customers_import
  group by customer_id
);

select count(*) as rows from customers_import

That deletes 3 rows and leaves 20. Run the "which keys repeat" query before and after, and wrap the delete in a transaction on a table that matters.

Another way, and the only one where an engine has no row id: build a clean copy with create table ... as select distinct (or the row_number query), check it, then swap it in for the original.

Stop them coming back

A primary key or unique constraint makes the database refuse a second row with the same key:

create or replace table customers_clean (
  customer_id integer primary key,
  first_name  varchar,
  email       varchar
);

insert into customers_clean
select customer_id, first_name, email
from customer_loads

That insert fails with PRIMARY KEY or UNIQUE constraint violation: duplicate key "2", and inserts nothing. Deduplicate on the way in, and later loads can skip rows that are already there with on conflict do nothing (see upserts):

insert into customers_clean
select distinct on (customer_id) customer_id, first_name, email
from customer_loads
order by customer_id, loaded_at desc;

insert into customers_clean
values (2, 'Nadia', 'nadia.reyes@example.com')
on conflict do nothing;

select count(*) as customers from customers_clean

Near duplicates

Rows that mean the same thing but aren't spelled the same ('Ana@Example.com' and 'ana@example.com ', with a trailing space) aren't duplicates to DISTINCT or GROUP BY. Group on a cleaned version of the key, such as lower(trim(email)), and look for names a typo apart with fuzzy matching. Treat what that finds as candidates to check, not rows to delete.

How other databases differ