How to Remove Duplicate Rows in SQL

Q: What is the fastest way to remove duplicates from a SQL query result?

SELECT DISTINCT column_name FROM table removes duplicate values in a query result without changing the underlying table. Use it whenever you just need a unique list back from a query, not a permanent fix to stored duplicate rows.

SQL has two different answers depending on what you actually need: removing duplicates from a query's output, or permanently deleting duplicate rows that are already stored in a table. They use different techniques, and mixing them up is the most common mistake here.

Quick answer

For a query result, SELECT DISTINCT column FROM table removes duplicate values without touching stored data. To permanently delete duplicate rows already in a table, use a CTE with ROW_NUMBER() OVER (PARTITION BY key_column ORDER BY some_column), then DELETE every row where the row number is greater than 1 - this keeps exactly one row per duplicate group.

Need the same thing in another language?

See the full reference for Python, JavaScript, Java, Bash, PowerShell, and more.

See every language →

Removing duplicates from a query result: DISTINCT

SELECT DISTINCT customer_id
FROM orders;

DISTINCT applies to every column in the SELECT list - if you select multiple columns, rows are only considered duplicates when every selected column matches. This does not modify the table; it only affects what the query returns.

The same thing with GROUP BY

SELECT customer_id
FROM orders
GROUP BY customer_id;

A bare GROUP BY on the columns you'd otherwise select produces the same deduplication effect as DISTINCT. GROUP BY is more commonly paired with an aggregate function (COUNT(*), MAX(created_at)) when you need a value from within each group, not just the distinct keys themselves.

Actually deleting duplicate rows from a table

WITH ranked AS (
  SELECT
    id,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY created_at DESC
    ) AS rn
  FROM customers
)
DELETE FROM customers
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

This is the standard pattern for permanent cleanup. ROW_NUMBER() OVER (PARTITION BY email ...) assigns 1, 2, 3... to each row within every group of matching emails, ordered by created_at DESC so the newest row in each group gets rank 1. The DELETE then removes every row with a rank greater than 1, keeping exactly one row (the newest) per email.

Postgres shortcut: DISTINCT ON

SELECT DISTINCT ON (email) *
FROM customers
ORDER BY email, created_at DESC;

Postgres-specific, but shorter than the window-function approach for the same job: it keeps one row per distinct email, and the tiebreaker is controlled by the ORDER BY (here, the newest row wins). Note that email must be the first column in the ORDER BY for this to work correctly.

Performance note

The ROW_NUMBER() approach scans and ranks every row in each partition, which is fine for a one-time cleanup but can be slow without an index on the partition column for very large tables. If duplicates keep reappearing, consider adding a unique constraint or index on the columns that should never repeat, so the database rejects new duplicates going forward instead of needing repeated cleanup.

Comparing exported files instead of live tables? Paste them here.

Open Remove Duplicate Lines →

Frequently Asked Questions

What is the fastest way to remove duplicates from a SQL query result?

SELECT DISTINCT column_name FROM table removes duplicate values in a query result without changing the underlying table. Use it whenever you just need a unique list back from a query, not a permanent fix to stored duplicate rows.

How do I actually delete duplicate rows from a table, not just a query result?

SELECT DISTINCT doesn't modify data - it only affects what a query returns. To delete stored duplicate rows, use a CTE with ROW_NUMBER() OVER (PARTITION BY ...) to number each duplicate group, then DELETE every row where the row number is greater than 1.

What's the difference between DISTINCT and GROUP BY for removing duplicates?

DISTINCT removes duplicate rows across all selected columns. GROUP BY groups rows sharing the same values and is typically used with an aggregate function (COUNT, MAX, SUM) - but a bare GROUP BY on the same columns you'd otherwise SELECT DISTINCT produces the same deduplication effect.

How do I keep only one row per duplicate group but choose which one?

ROW_NUMBER() OVER (PARTITION BY key_column ORDER BY some_column DESC) lets you control which row is kept as rank 1 within each group - for example ORDER BY updated_at DESC keeps the most recently updated row per duplicate group.

Does Postgres have a shortcut for this?

Yes - DISTINCT ON (column) lets you pick one row per distinct value of column directly in a SELECT, combined with an ORDER BY to control which row wins. It's Postgres-specific syntax, not standard SQL, but it's shorter than a window function for this exact use case.

Will removing duplicate rows this way affect performance on a large table?

The DELETE-with-ROW_NUMBER() approach scans and numbers every row in the affected partition, which can be slow on very large tables without an index on the partition columns. For a one-time cleanup this is usually acceptable; for repeated cleanup, consider adding a unique constraint to prevent duplicates going forward.

What if my two datasets are exported files, not live database tables?

If you're comparing two CSV or spreadsheet exports rather than querying a live database, a SQL query won't help directly. Paste both into Column Compare instead to match rows by a key column.