SQL · Guides · October 30, 2026 · 12 min read

How to find and delete duplicates in SQL, with examples

To find duplicates in SQL, group by the columns that should be unique and keep the groups that occur more than once: GROUP BY col1, col2 HAVING COUNT(*) > 1. To see the full duplicate rows, or to delete all but one copy, number the rows inside each group with ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY ...): row 1 is the copy you keep, rows 2 and above are the duplicates. The hard part is not the syntax but deciding which columns define “the same row”, and which copy should survive.

All examples run on the airline database from the Nomto SQL course, in PostgreSQL syntax on DuckDB (the engine of the Nomto SQL room), and every result table is the real output. The SQL room is read-only, so the DELETE statements near the end are shown as syntax; the SELECTs that preview what they would remove were run.

Start by defining what a duplicate is

The app_events table holds 1,494,723 events from the mobile apps and the website: app opens, searches, offer views, purchases. Every row has its own event_id, so a check for exact copies finds nothing:

SELECT COUNT(*) AS all_rows,
       (SELECT COUNT(*) FROM (SELECT DISTINCT * FROM app_events) d) AS distinct_rows
FROM app_events;

That does not mean the table is clean. When a phone loses its connection, the app resends the event, and the server stores it again with a new event_id and a later received_at. The event itself, one client doing one thing at one moment, is the same. So the business key here is (client_id, event_time, event_name), and the surrogate columns event_id and received_at have to be left out of the comparison.

This is the first decision in any duplicate hunt: list the columns that, by the meaning of the data, must be unique. Including an ID column in the comparison hides every duplicate.

Find duplicate values in one column

The simplest case is one column. Which passenger names appear on more than one ticket?

SELECT passenger_name, COUNT(*) AS tickets
FROM tickets
GROUP BY passenger_name
HAVING COUNT(*) > 1
ORDER BY tickets DESC
LIMIT 3;

15,389 names repeat. None of this is a data error: the 366,733 tickets carry only 22,220 distinct names, and common names are shared by many people. GROUP BY collapses the rows with the same value, HAVING filters the groups after counting (see GROUP BY vs HAVING for why WHERE COUNT(*) > 1 does not work).

Find duplicates across several columns

Put every column of the key into GROUP BY. Retried events in app_events:

SELECT client_id, event_time, event_name, COUNT(*) AS copies
FROM app_events
GROUP BY client_id, event_time, event_name
HAVING COUNT(*) > 1
ORDER BY event_time
LIMIT 5;

How many duplicates are there in total?

Wrap the same query and sum. The number of groups and the number of extra rows are different things, and reports often mix them up:

SELECT COUNT(*) AS duplicate_groups,
       SUM(copies) AS rows_in_groups,
       SUM(copies - 1) AS extra_rows
FROM (
  SELECT client_id, event_time, event_name, COUNT(*) AS copies
  FROM app_events
  GROUP BY client_id, event_time, event_name
  HAVING COUNT(*) > 1
) d;

Every duplicated event here has exactly two copies, so 21,378 rows are involved but only 10,689 of them need to go. A one-line check gives the same answer without listing groups:

SELECT COUNT(*) AS rows,
       COUNT(DISTINCT (client_id, event_time, event_name)) AS unique_events,
       COUNT(*) - COUNT(DISTINCT (client_id, event_time, event_name)) AS extra
FROM app_events;

COUNT(DISTINCT (a, b, c)) with a row in parentheses works in PostgreSQL and DuckDB; MySQL writes it as COUNT(DISTINCT a, b, c). SQL Server allows only one expression in COUNT(DISTINCT ...), so there use the subquery version.

Not every repeat is a duplicate

Define the key carelessly and you will “find” duplicates that are real data. The same name on the same flight looks suspicious:

SELECT tf.flight_id, t.passenger_name,
       COUNT(*) AS tickets,
       COUNT(DISTINCT t.passenger_id) AS passengers
FROM ticket_flights tf
JOIN tickets t ON t.ticket_no = tf.ticket_no
GROUP BY tf.flight_id, t.passenger_name
HAVING COUNT(*) > 1
ORDER BY tickets DESC, tf.flight_id
LIMIT 3;

There are 12,169 such flight-and-name pairs, but each ticket has a different passenger_id: five different people called Aleksandr Ivanov on flight 298. Adding COUNT(DISTINCT id) next to COUNT(*) is a quick way to tell true duplicates from namesakes.

Show the full duplicate rows

GROUP BY returns one line per group, and you lose the other columns. To see every copy with all its columns, count with a window function instead, which keeps the rows (more in SQL window functions):

SELECT event_id, client_id, event_time, received_at, event_name,
       COUNT(*) OVER (PARTITION BY client_id, event_time, event_name) AS copies
FROM app_events
QUALIFY copies > 1
ORDER BY event_time, client_id, received_at
LIMIT 6;

Now the pattern is visible: same moment, same action, but the second copy arrived later. Across all 10,689 pairs the resend came between 1 second and 10 minutes after the original, 24 seconds at the median.

QUALIFY filters on a window function the way HAVING filters on an aggregate. DuckDB, Snowflake, BigQuery and Databricks support it; PostgreSQL, MySQL and SQL Server do not, so there put the window in a subquery and filter outside: SELECT * FROM (SELECT ..., COUNT(*) OVER (...) AS copies FROM app_events) x WHERE copies > 1.

An older alternative is to join the table to the GROUP BY ... HAVING result on all key columns. It works everywhere but reads the table twice.

Keep the first row with ROW_NUMBER

To deduplicate, number the copies inside each group and keep number 1. The ORDER BY inside OVER decides which copy wins; here the first one received, with event_id as a tie-breaker so the result is repeatable:

WITH ranked AS (
  SELECT event_id, client_id, event_time, received_at, event_name,
         ROW_NUMBER() OVER (
           PARTITION BY client_id, event_time, event_name
           ORDER BY received_at, event_id
         ) AS rn
  FROM app_events
)
SELECT rn, COUNT(*) AS rows
FROM ranked
GROUP BY rn
ORDER BY rn;

WHERE rn = 1 gives the clean table of 1,484,034 events; WHERE rn > 1 gives exactly the 10,689 rows to delete. Without a deterministic ORDER BY, the database may keep a different copy each time you run the query.

Which copy to keep depends on the data. For resent events, the earliest is the original. For a customer table loaded several times, you usually want the latest version: ORDER BY updated_at DESC. For rows that differ in completeness, you might order by “has an email” first.

Why it matters: inflated metrics

Duplicates rarely break a query; they quietly inflate counts. Purchases by platform, raw and deduplicated:

WITH ranked AS (
  SELECT platform,
         ROW_NUMBER() OVER (
           PARTITION BY client_id, event_time, event_name
           ORDER BY received_at, event_id
         ) AS rn
  FROM app_events
  WHERE event_name = 'purchase'
)
SELECT platform,
       COUNT(*) AS raw_purchases,
       COUNT(*) FILTER (WHERE rn = 1) AS real_purchases,
       COUNT(*) - COUNT(*) FILTER (WHERE rn = 1) AS duplicates
FROM ranked
GROUP BY platform
ORDER BY platform;

Android has the most resends: its raw purchase count is about 1% too high, more than double the web’s overcount of 0.4%. Small, but enough to shift a conversion rate or an A/B test comparison between platforms.

DISTINCT ON: one row per key in PostgreSQL

PostgreSQL and DuckDB have a shortcut for “first row per group”: DISTINCT ON (key) keeps the first row of each key according to ORDER BY. The ORDER BY must start with the same columns as DISTINCT ON:

SELECT DISTINCT ON (client_id, event_time, event_name)
       event_id, client_id, event_time, event_name, received_at
FROM app_events
WHERE event_name = 'purchase'
ORDER BY client_id, event_time, event_name, received_at, event_id
LIMIT 3;

Run over the whole table, it returns the same 1,484,034 rows as rn = 1. DISTINCT ON is shorter, but MySQL, SQL Server and Oracle do not have it, and it only handles “keep one”; ROW_NUMBER also lets you keep two, or flag duplicates without removing them.

Plain SELECT DISTINCT removes duplicates only when every selected column matches, so it helps with exact copies and does nothing here, where event_id and received_at differ.

Delete duplicate rows

Before deleting anything, run the SELECT that lists the victims and check the count. For the retried events, the rows to remove are those with rn > 1:

SELECT COUNT(*) AS rows_to_delete
FROM app_events
WHERE event_id IN (
  SELECT event_id
  FROM (
    SELECT event_id,
           ROW_NUMBER() OVER (
             PARTITION BY client_id, event_time, event_name
             ORDER BY received_at, event_id
           ) AS rn
    FROM app_events
  ) r
  WHERE rn > 1
);

The number matches extra_rows from the GROUP BY check, so the delete will remove exactly one copy from each pair. Turning it into a DELETE depends on the database.

PostgreSQL and DuckDB

When the table has a unique ID, delete by ID with the same subquery:

DELETE FROM app_events
WHERE event_id IN (
  SELECT event_id
  FROM (
    SELECT event_id,
           ROW_NUMBER() OVER (
             PARTITION BY client_id, event_time, event_name
             ORDER BY received_at, event_id
           ) AS rn
    FROM app_events
  ) r
  WHERE rn > 1
);

If a PostgreSQL table has no unique column at all (exact copies), use the hidden physical row address ctid to tell copies apart:

DELETE FROM t a
USING t b
WHERE a.key_col = b.key_col
  AND a.ctid > b.ctid;   -- keeps the copy with the smallest ctid

In DuckDB, rebuilding the table is often simpler than deleting from it:

CREATE OR REPLACE TABLE app_events AS
SELECT * FROM app_events
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY client_id, event_time, event_name
  ORDER BY received_at, event_id
) = 1;

SQL Server

SQL Server lets you delete through a CTE that reads a single table, which makes this the shortest version:

WITH ranked AS (
  SELECT ROW_NUMBER() OVER (
           PARTITION BY client_id, event_time, event_name
           ORDER BY received_at, event_id
         ) AS rn
  FROM app_events
)
DELETE FROM ranked
WHERE rn > 1;

MySQL

MySQL refuses a DELETE whose subquery selects from the same table directly (error 1093, “You can’t specify target table for update in FROM clause”). Join to a derived table instead. In MySQL 8.0 and later, with window functions:

DELETE e
FROM app_events e
JOIN (
  SELECT event_id,
         ROW_NUMBER() OVER (
           PARTITION BY client_id, event_time, event_name
           ORDER BY received_at, event_id
         ) AS rn
  FROM app_events
) r ON r.event_id = e.event_id
WHERE r.rn > 1;

In MySQL 5.7, which has no window functions, use a self join: delete a row when another row with the same key arrived earlier.

DELETE e1
FROM app_events e1
JOIN app_events e2
  ON  e2.client_id  = e1.client_id
  AND e2.event_time = e1.event_time
  AND e2.event_name = e1.event_name
  AND (e2.received_at < e1.received_at
       OR (e2.received_at = e1.received_at AND e2.event_id < e1.event_id));

Whichever database you use, run the delete inside a transaction, compare the affected row count with your SELECT, and only then commit. On large tables, deleting in batches or rebuilding the table avoids long locks.

Stop duplicates from coming back

Deleting fixes the past; a constraint fixes the future. A unique index on the business key makes the database reject the second copy:

CREATE UNIQUE INDEX app_events_dedup
  ON app_events (client_id, event_time, event_name);

Then make the loader tolerate repeats instead of failing: INSERT ... ON CONFLICT DO NOTHING in PostgreSQL and DuckDB, INSERT IGNORE or INSERT ... ON DUPLICATE KEY UPDATE in MySQL, MERGE in SQL Server. Creating the index fails while duplicates still exist, so clean first. If you cannot change the source table, deduplicate in a view or a staging model with the ROW_NUMBER pattern, and point every report at that.

One more trap: GROUP BY puts all NULLs in one group, so rows with NULL in a key column count as duplicates of each other. A self join with a.col = b.col never matches NULLs and misses them. A unique index treats NULLs as distinct in PostgreSQL by default (PostgreSQL 15 added NULLS NOT DISTINCT to change that). Decide which behaviour you want before trusting the count. More on this in NULL in SQL.

FAQ

How do I find duplicate records in SQL?

Group by the columns that should be unique and filter with HAVING COUNT(*) > 1: SELECT col1, col2, COUNT(*) FROM t GROUP BY col1, col2 HAVING COUNT(*) > 1. To see the complete rows, use COUNT(*) OVER (PARTITION BY col1, col2) and filter where it is greater than 1.

How do I delete duplicates but keep one row?

Number the rows in each group with ROW_NUMBER() OVER (PARTITION BY key ORDER BY ...) and delete those with a number above 1. The ORDER BY decides which copy survives, so make it deterministic, for example the earliest timestamp plus the ID as a tie-breaker.

How do I remove duplicates without deleting them?

Select only the first copy: WHERE rn = 1 over a ROW_NUMBER subquery, DISTINCT ON in PostgreSQL and DuckDB, or SELECT DISTINCT if the rows are identical in every column. Wrap it in a view so reports read the clean version while the raw table stays untouched.

Why does SELECT DISTINCT not remove my duplicates?

DISTINCT compares all selected columns. If any of them differs, such as an ID or a load timestamp, the rows are not identical and both stay. Select only the key columns, or use ROW_NUMBER to pick one full row per key.

What is the fastest way to delete duplicates from a large table?

Deleting a small share of rows by ID is usually fine. When a large share is duplicated, it is often faster to create a new table with only rn = 1 rows, swap it in, and add a unique index so the problem does not return.

Also in Español, Português (Brasil), Tiếng Việt, 简体中文