ANSI SQL / UniversalQueries & Filtering

How to Find and Delete Duplicate Rows in SQL

Identify rows with duplicate column values using GROUP BY and HAVING count(*) > 1, and safely remove duplicates using ROW_NUMBER() CTEs.

Quick SQL Solution

Safe (Read / Non-destructive)ANSI SQL / UniversalBeginner

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

When to use this scenario:A bug in sign-up or import logic created duplicate user email records or identical order entries in your database.

Step-by-Step Query Breakdown

1Find all duplicate emails and count

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Groups rows by the email column and filters out groups with only 1 occurrence, showing which values are duplicated.

2View all columns of the duplicated rows

SELECT *
FROM users
WHERE email IN (
  SELECT email
  FROM users
  GROUP BY email
  HAVING COUNT(*) > 1
)
ORDER BY email, created_at;

Subquery returns all full row records belonging to duplicated groups for manual comparison.

3Safely delete duplicates keeping the oldest row (Postgres / SQL Server)

WITH ranked_duplicates AS (
  SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id ASC) as row_num
  FROM users
)
DELETE FROM users
WHERE id IN (
  SELECT id FROM ranked_duplicates WHERE row_num > 1
);

Assigns a row rank per partition. Keeps row_num = 1 and permanently deletes subsequent duplicates.

Critical SQL Pitfalls & Precautions

•Always test your DELETE query with a SELECT first inside a BEGIN TRANSACTION / ROLLBACK block.
•If your table lacks a unique primary key (ID), use the database physical row identifier (ctid in PostgreSQL, rowid in SQLite).

Frequently Asked Questions

Find & Delete Duplicate Rows - Frequently Asked Questions

Common questions about transactional safety, indexing, and engine compatibility.

Add a UNIQUE constraint or unique index: "ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);".

Related SQL Query Guides

Browse All SQL Recipes

ANSI SQL / UniversalDestructive

DELETE vs TRUNCATE vs DROP in SQL: Differences Explained

Compare row-by-row logging (DELETE), fast table emptying with auto-increment reset (TRUNCATE), and complete table removal (DROP).

PostgreSQLSafe

How to UPSERT in PostgreSQL Using ON CONFLICT DO UPDATE

Atomically insert a new row or update an existing row if a unique constraint or primary key conflict is encountered.

ANSI SQL / UniversalSafe

How to Calculate a Running Total (Cumulative Sum) in SQL

Compute cumulative running totals over time or partitioned groups using SUM() OVER (ORDER BY ...).

PostgreSQLSafe

How to Create an Index Concurrently in PostgreSQL (Zero Downtime)

Build a production database index on a table with millions of rows without locking out live INSERT, UPDATE, or DELETE operations.

ANSI SQL / UniversalReversible

How to UPDATE a Table From Another Table in SQL

Update values in one table based on matching columns or aggregations in a secondary table.

ANSI SQL / UniversalReversible

How to Reset Auto Increment / Sequence in MySQL & PostgreSQL

Reset the next auto-generated primary key ID back to 1 or synchronize it with the maximum existing ID in the table.