Data Cleansing in SQL: Find and Remove Duplicate Rows

Duplicates are the most common defect in any dataset, and they're especially common in scraped data, where the same listing can be captured twice from paginated or overlapping pages, or merged across crawling runs. Data cleansing in SQL starts here: find the duplicates, see exactly which rows they are, then remove the extras without touching the one you want to keep. This guide gives you copy-paste queries for MySQL, PostgreSQL, SQL Server, and Oracle, and finishes with how to stop duplicates coming back.

Throughout, assume a simple table:

CREATE TABLE contacts (
  id   INT PRIMARY KEY,
  name VARCHAR(255),
  email VARCHAR(255)
);

Step 1: Find duplicate rows

The classic approach groups by the column that should be unique and keeps only groups that appear more than once:

SELECT name, COUNT(*) AS duplicate_count
FROM contacts
GROUP BY name
HAVING COUNT(*) > 1;

HAVING filters groups (after aggregation), whereas WHERE filters rows (before it) — that's why the count test has to live in HAVING. Use COUNT(*) > 1 rather than the alias in the HAVING clause: MySQL tolerates the alias, but PostgreSQL and Oracle do not, so COUNT(*) is the portable form.

This tells you what is duplicated, but not which rows. If you need to act on them, you need their ids.

Step 2: Print out the ids of the duplicates

Adding id straight into the SELECT above won't work — once you GROUP BY name, there are several ids per group. The fix is to aggregate the ids into one value per group. Each database has its own function for this:

MySQL — GROUP_CONCAT:

SELECT name,
       COUNT(*) AS duplicate_count,
       GROUP_CONCAT(id ORDER BY id) AS ids
FROM contacts
GROUP BY name
HAVING COUNT(*) > 1;

PostgreSQL — STRING_AGG (or ARRAY_AGG for an array):

SELECT name,
       COUNT(*) AS duplicate_count,
       STRING_AGG(id::text, ',' ORDER BY id) AS ids
FROM contacts
GROUP BY name
HAVING COUNT(*) > 1;

SQL Server 2017+ — STRING_AGG:

SELECT name,
       COUNT(*) AS duplicate_count,
       STRING_AGG(CAST(id AS varchar(20)), ',') AS ids
FROM contacts
GROUP BY name
HAVING COUNT(*) > 1;

Oracle — LISTAGG:

SELECT name,
       COUNT(*) AS duplicate_count,
       LISTAGG(id, ',') WITHIN GROUP (ORDER BY id) AS ids
FROM contacts
GROUP BY name
HAVING COUNT(*) > 1;

The result reads like this — one row per duplicated name, with the offending ids collected into a string:

+--------------+-----------------+-----------+
| name         | duplicate_count | ids       |
+--------------+-----------------+-----------+
| Ada Lovelace | 2               | 1148,1149 |
| Alan Turing  | 2               | 1201,1202 |
| Grace Hopper | 3               | 1178,1179,1190 |
+--------------+-----------------+-----------+

Now you can see exactly which rows to review or delete.

Step 3: Duplicates across several columns

Real duplicates rarely hinge on one field. Two contacts are the "same" only if name and email match — so group by every column that defines identity:

SELECT name, email, COUNT(*) AS duplicate_count
FROM contacts
GROUP BY name, email
HAVING COUNT(*) > 1;

Watch out for near-duplicates that differ only by whitespace or case ("Ada " vs "ada"). Normalize inside the group to catch them:

GROUP BY LOWER(TRIM(name)), LOWER(TRIM(email))

Step 4: The modern approach — window functions

GROUP BY collapses rows, which makes it awkward when you want to keep the original rows and just tag the extras. ROW_NUMBER() does exactly that, and it works in MySQL 8+, PostgreSQL, SQL Server, and Oracle:

SELECT *
FROM (
  SELECT id, name, email,
         ROW_NUMBER() OVER (PARTITION BY name, email ORDER BY id) AS rn
  FROM contacts
) t
WHERE rn > 1;

Within each set of duplicates, ROW_NUMBER() numbers the rows 1, 2, 3… ordered by id. Row 1 is the one you keep (the lowest id); every row with rn > 1 is a duplicate to remove. This is the foundation for a safe delete.

Step 5: Remove duplicates safely

Always run the SELECT version first and eyeball what it returns before you delete anything. Then wrap the delete in a transaction so you can roll back.

PostgreSQL (delete via a CTE):

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

SQL Server (delete straight from the CTE):

WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (PARTITION BY name, email ORDER BY id) AS rn
  FROM contacts
)
DELETE FROM ranked WHERE rn > 1;

MySQL 8+ — MySQL won't let you delete from a table you're also selecting from directly, so use a self-join that keeps the minimum id per group:

DELETE c
FROM contacts c
JOIN (
  SELECT name, email, MIN(id) AS keep_id
  FROM contacts
  GROUP BY name, email
  HAVING COUNT(*) > 1
) d
  ON c.name = d.name
 AND c.email = d.email
 AND c.id <> d.keep_id;

PostgreSQL without a unique id — if the table has no primary key, use the hidden physical row identifier ctid:

DELETE FROM contacts a
USING contacts b
WHERE a.name = b.name
  AND a.email = b.email
  AND a.ctid  > b.ctid;

Each of these keeps one row per duplicate group and deletes the rest.

Step 6: Stop duplicates coming back

Cleaning is only worth it if the mess doesn't return. Once the table is clean, enforce uniqueness at the schema level:

ALTER TABLE contacts
ADD CONSTRAINT uq_contacts UNIQUE (name, email);

Then make inserts idempotent so a re-scrape can't create dupes:

• PostgreSQL / SQLite: INSERT ... ON CONFLICT (name, email) DO NOTHING;
• MySQL: INSERT IGNORE ... or INSERT ... ON DUPLICATE KEY UPDATE ...
• SQL Server: MERGE, or a WHERE NOT EXISTS guard on the insert.

With a unique constraint in place, the database rejects duplicates for you — no cleanup script required.

Data cleansing in SQL beyond duplicates

Deduplication is one pillar of data cleansing in SQL; the same table usually needs a few more passes before it's trustworthy:

• Trim and normalize text: UPDATE contacts SET name = TRIM(name); and lower-case where appropriate to keep joins and grouping honest. Broader data normalization — units, dates, currencies — is a topic in itself.
• Turn blanks into NULLs: NULLIF(email, '') so empty strings don't masquerade as data.
• Standardize formats: consistent casing, phone/date formats, and currency stripped of symbols before numeric conversion.
• Validate ranges: flag negative prices or impossible dates with a CHECK constraint or a WHERE audit query.

Do the text normalization before the dedup step — otherwise "Ada " and "Ada" slip through as distinct rows.

FAQ

How do I find duplicate rows in SQL? GROUP BY the columns that define a duplicate and keep groups with HAVING COUNT(*) > 1. That lists which values repeat.

How do I see the ids of the duplicate rows? Aggregate them per group: GROUP_CONCAT in MySQL, STRING_AGG in PostgreSQL and SQL Server, LISTAGG in Oracle.

What's the safest way to delete duplicates but keep one? Number the rows with ROW_NUMBER() OVER (PARTITION BY <key> ORDER BY id) and delete every row where the number is greater than 1. Preview with a SELECT and run it inside a transaction first.

GROUP BY or window functions — which should I use? GROUP BY/HAVING is perfect for finding and counting duplicates. ROW_NUMBER() is better for removing them, because it preserves individual rows so you can target just the extras.

Deduplication is a small slice of turning raw scrapes into a reliable dataset — you also need normalization, validation, and refreshes. If you'd rather receive data that's already cleansed and deduplicated, scraping.pro delivers it as data as a service.