Skip to content
Cheatsheets / SQL Deduplication Cheatsheet
Cheatsheet

SQL Deduplication Cheatsheet

Find duplicates, keep one row per key, delete copies, and prevent them.

Which kind of duplicate?

The rows are Use
Identical in every column select distinct *
The same key, other columns differ row_number() per key, keep rn = 1
Spelled differently (case, spaces, typos) Group on lower(trim(col)); check typos by hand
Real repeats, such as two orders from one customer Not duplicates. Count them; don't remove them

Find

Keys that appear more than once 
select customer_id, count(*) as orders
from orders
group by customer_id
having count(*) > 1
order by orders desc, customer_id
Rows against distinct keys 
select count(*)                    as rows,
       count(distinct customer_id) as customers
from orders

Keep one row per key

Portable: row_number in a CTE 
with ranked as (
  select *,
         row_number() over (
           partition by customer_id
           order by order_date desc, order_id desc
         ) as rn
  from orders
)
select * from ranked where rn = 1
DuckDB, Snowflake, BigQuery: QUALIFY 
select *
from orders
qualify row_number() over (
  partition by customer_id
  order by order_date desc, order_id desc
) = 1
DuckDB, PostgreSQL: DISTINCT ON 
select distinct on (customer_id) *
from orders
order by customer_id, order_date desc, order_id desc
DuckDB: one column from the latest row 
select customer_id,
       arg_max(order_id, order_date) as latest_order
from orders
group by customer_id

Each returns Garden Shop's 16 customers with their latest order. The window or ORDER BY says which row wins; end it with a unique column so two rows can never tie.

Remove copies from a table

DuckDB and SQLite: delete by rowid 
create or replace table orders_load as
select * from orders
union all
select * from orders where order_id <= 3;

delete from orders_load
where rowid not in (
  select min(rowid)
  from orders_load
  group by order_id
);

select count(*) as rows from orders_load
Any engine: rebuild, check, swap 
create or replace table orders_clean as
select distinct * from orders_load;

select count(*) as rows from orders_clean

Prevent

A key, and inserts that skip repeats 
create or replace table order_keys (
  order_id integer primary key,
  status   varchar
);

insert into order_keys
select order_id, status from orders;

insert into order_keys values (1, 'shipped')
on conflict do nothing;
Watch out

row_number() without an ORDER BY keeps an arbitrary row, and the highest id isn't always the latest row. DISTINCT removes nothing if any column differs, even a load timestamp. A self-join on every column misses copies with a NULL in them, because NULL = NULL isn't true. And select min(rowid) ... group by all groups the whole table into one row. See the deduplication reference for each.

Related

Reference
SQL Deduplication Patterns

Find duplicates, keep one row per key, and delete copies.

Interview
SQL Deduplication Interview Questions

Practice DISTINCT, ROW_NUMBER, and choosing the keeper row.

Mistake
SQL: Why DISTINCT ON Returns the Wrong Row

Sort by the deciding column after the key.

End of cheatsheets