Duplicate records silently corrupt every report and join that touches them — often without raising an error. Learn how to detect exact and partial duplicates with GROUP BY and HAVING, then safely remove them using ROW_NUMBER() subquery patterns you can trust with production data.
Duplicates are one of the most common data quality problems you'll encounter in production databases. A CRM gets records imported twice from a spreadsheet. An ETL job runs twice because of a retry after a timeout. A form submission fires twice because a user clicked the button at the same moment the network recovered. However they got there, duplicate records silently corrupt every aggregate, every report, and every join that touches them — often without raising a single error.
By the end of this lesson, you'll be able to do two distinct things: find duplicates precisely, regardless of whether they're exact copies or "logical" duplicates sharing only certain fields, and then remove them safely, keeping exactly the record you want. These are different problems that require different tools, and conflating them is where most people go wrong.
This lesson uses realistic scenarios across two domains — a customer database and an order log — because duplicate problems tend to look different depending on whether you're working with reference data or transactional data. You'll walk away with patterns you can drop into a real codebase with minimal modification.
What you'll learn:
GROUP BY and HAVINGROW_NUMBER() to identify which specific records to removeYou should be comfortable with SELECT, WHERE, and basic GROUP BY before starting here. If you need a refresher, see SQL Basics: Master SELECT, FROM, WHERE Clauses and Build Your First Queries. This lesson also uses subqueries — you don't need to be an expert, but you should understand the basic concept. Understanding SQL Subqueries: Filtering and Looking Up Data with Nested SELECT Statements covers the foundation if you need it.
Let's work with a customers table that a sales team uses to track leads and accounts. An import script ran twice last week and nobody noticed until a rep called the same prospect twice in one afternoon.
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
email VARCHAR(255),
first_name VARCHAR(100),
last_name VARCHAR(100),
company VARCHAR(200),
created_at TIMESTAMP,
source VARCHAR(50)
);
Here's a representative slice of what's in there:
| customer_id | first_name | last_name | company | created_at | source | |
|---|---|---|---|---|---|---|
| 1001 | sara.chen@acme.com | Sara | Chen | Acme Corp | 2024-01-15 09:00:00 | webform |
| 1002 | j.patel@globex.io | Jay | Patel | Globex | 2024-01-15 09:01:00 | import |
| 1003 | sara.chen@acme.com | Sara | Chen | Acme Corp | 2024-01-15 09:03:00 | import |
| 1004 | m.torres@pinnacle.co | Maria | Torres | Pinnacle | 2024-01-16 10:15:00 | import |
| 1005 | m.torres@pinnacle.co | Maria | Torres | Pinnacle | 2024-01-16 10:15:00 | import |
| 1006 | ben.wu@startup.io | Ben | Wu | StartupCo | 2024-01-17 14:22:00 | webform |
Rows 1001/1003 and 1004/1005 are duplicates. But notice they're slightly different kinds: Sara Chen has two different customer_ids and slightly different timestamps (she might have submitted the form and been imported). Maria Torres has two records that are nearly identical, same timestamp, clearly a double-write from a retry.
This matters when you decide which record to keep.
The first question is always: how bad is it? Before you touch anything, audit the scope.
SELECT
email,
COUNT(*) AS record_count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY record_count DESC;
Output:
| record_count | |
|---|---|
| m.torres@pinnacle.co | 2 |
| sara.chen@acme.com | 2 |
This is the foundational duplicate-detection pattern. GROUP BY email collapses all rows with the same email into a single group, COUNT(*) tells you how many rows fell into that group, and HAVING COUNT(*) > 1 filters to only the groups with more than one — the duplicates.
The HAVING clause is what makes this work. WHERE filters individual rows before aggregation. HAVING filters groups after aggregation. You cannot write WHERE COUNT(*) > 1 — that's a syntax error because COUNT(*) doesn't exist at the WHERE evaluation stage. If you want to go deeper on this distinction, Filtering Groups After Aggregation: Writing HAVING Clauses That Answer Real Business Questions covers the mechanics thoroughly.
Key insight
This query tells you which emails have duplicates, not which rows are duplicates. That sounds like a minor distinction but it drives everything that comes next. Your next job is to retrieve the actual rows behind those email addresses.
Now that you know which emails are duplicated, pull all the rows associated with them. The cleanest way is a subquery in an IN clause:
SELECT
customer_id,
email,
first_name,
last_name,
created_at,
source
FROM customers
WHERE email IN (
SELECT email
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
)
ORDER BY email, created_at;
Output:
| customer_id | first_name | last_name | created_at | source | |
|---|---|---|---|---|---|
| 1001 | sara.chen@acme.com | Sara | Chen | 2024-01-15 09:00:00 | webform |
| 1003 | sara.chen@acme.com | Sara | Chen | 2024-01-15 09:03:00 | import |
| 1004 | m.torres@pinnacle.co | Maria | Torres | 2024-01-16 10:15:00 | import |
| 1005 | m.torres@pinnacle.co | Maria | Torres | 2024-01-16 10:15:00 | import |
Now you can see the actual rows and make decisions. For Sara Chen, the webform record came first — that's likely the canonical one. For Maria Torres, the records are identical (same timestamp) — you'd keep whichever has the lower customer_id (the earlier-assigned one) by convention.
Tip
Always run this read-only retrieval query first and review the output before writing any DELETE or UPDATE. Duplicate scenarios often reveal data quality surprises you didn't expect — like rows that are almost duplicates but have different phone numbers, suggesting two people at the same company.
Email is a clean deduplication key for customer data, but many real problems require matching on combinations of columns. Consider an orders table where the same order might be logged twice due to a message-queue retry:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
product_sku VARCHAR(50),
quantity INT,
order_total DECIMAL(10,2),
placed_at TIMESTAMP
);
Here you can't just match on customer_id — the same customer places many orders. You need to match on the combination of customer_id, product_sku, and placed_at (within a narrow time window) to identify a retry duplicate.
SELECT
customer_id,
product_sku,
placed_at,
COUNT(*) AS record_count,
MIN(order_id) AS earliest_order_id,
MAX(order_id) AS latest_order_id
FROM orders
GROUP BY
customer_id,
product_sku,
placed_at
HAVING COUNT(*) > 1
ORDER BY record_count DESC;
By including MIN(order_id) and MAX(order_id) in the SELECT, you're already surfacing the information you'll need for the deletion step — the earliest (canonical) record and the duplicate.
Warning
Be careful about what you include in GROUP BY when detecting partial duplicates. If you group by too many columns, near-duplicates that differ in one minor field won't be caught. If you group by too few, you'll incorrectly flag legitimate rows as duplicates. Define "duplicate" in business terms first, then translate that definition into your column list.
Before any remediation, produce a summary that tells you the total scale:
SELECT
COUNT(*) AS total_rows,
COUNT(DISTINCT email) AS unique_emails,
COUNT(*) - COUNT(DISTINCT email) AS excess_rows,
ROUND(
100.0 * (COUNT(*) - COUNT(DISTINCT email)) / COUNT(*),
2
) AS duplicate_pct
FROM customers;
Output (hypothetical with a larger dataset):
| total_rows | unique_emails | excess_rows | duplicate_pct |
|---|---|---|---|
| 24183 | 23941 | 242 | 1.00 |
This is useful for two reasons: it gives you a before snapshot to validate against after deduplication, and it tells you whether you're dealing with a small clean-up or a systemic problem that needs a process fix upstream. One percent is common after a bad import. Ten percent means something structural is broken.
The GROUP BY / HAVING approach is excellent for finding duplicates, but it doesn't directly give you a list of row IDs to delete. For that, you need a window function: ROW_NUMBER().
SELECT
customer_id,
email,
first_name,
created_at,
source,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY created_at ASC, customer_id ASC
) AS row_num
FROM customers;
Output (subset):
| customer_id | first_name | created_at | source | row_num | |
|---|---|---|---|---|---|
| 1001 | sara.chen@acme.com | Sara | 2024-01-15 09:00:00 | webform | 1 |
| 1003 | sara.chen@acme.com | Sara | 2024-01-15 09:03:00 | import | 2 |
| 1002 | j.patel@globex.io | Jay | 2024-01-15 09:01:00 | import | 1 |
| 1004 | m.torres@pinnacle.co | Maria | 2024-01-16 10:15:00 | import | 1 |
| 1005 | m.torres@pinnacle.co | Maria | 2024-01-16 10:15:00 | import | 2 |
| 1006 | ben.wu@startup.io | Ben | 2024-01-17 14:22:00 | webform | 1 |
PARTITION BY email restarts the row counter for each unique email group. ORDER BY created_at ASC means the earliest record gets row_num = 1 — the one you want to keep. Every row with row_num > 1 is a candidate for deletion.
Key insight
The ORDER BY inside OVER() is your "which one do I keep" logic. Ordering by created_at ASC keeps the oldest record. Change it to DESC to keep the newest. You can also order by a source priority (using a CASE expression) if webform submissions should take precedence over import rows regardless of timing. Locking in this logic explicitly prevents ambiguity.
Now wrap it in a subquery to extract only the duplicate rows:
SELECT customer_id
FROM (
SELECT
customer_id,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY created_at ASC, customer_id ASC
) AS row_num
FROM customers
) ranked
WHERE row_num > 1;
This gives you a clean list of customer_id values to delete — and nothing else.
Now you're ready to actually remove the duplicates. The safest pattern is a DELETE with a subquery that references the ROW_NUMBER() logic you already validated:
-- Preview first: confirm these are the rows you want to remove
SELECT *
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM (
SELECT
customer_id,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY created_at ASC, customer_id ASC
) AS row_num
FROM customers
) ranked
WHERE row_num > 1
);
Run this SELECT first. Make sure the output looks exactly right. Then swap out SELECT * for the deletion:
DELETE FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM (
SELECT
customer_id,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY created_at ASC, customer_id ASC
) AS row_num
FROM customers
) ranked
WHERE row_num > 1
);
Warning
In MySQL specifically, you cannot directly reference the table you're deleting from in a subquery in the same statement without wrapping it in an intermediate derived table — which is exactly what the double-nested structure above does. In PostgreSQL and SQL Server, you can use a CTE with DELETE directly (shown below), which is cleaner. Know your database's rules before running this.
For PostgreSQL, a CTE-based delete is much more readable:
WITH duplicates AS (
SELECT
customer_id,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY created_at ASC, customer_id ASC
) AS row_num
FROM customers
)
DELETE FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM duplicates
WHERE row_num > 1
);
If you want to go deeper on DELETE safety patterns, INSERT, UPDATE, DELETE: Master SQL Data Modification Safely covers transaction wrapping and rollback strategies that are worth pairing with any bulk deletion operation.
Sometimes you can't or shouldn't delete from the source table. Maybe it's a raw ingestion table that's append-only by design. Maybe you don't have write access. In these cases, you deduplicate at query time using a derived table or CTE that filters to row_num = 1.
-- Reusable "clean" customer view
CREATE VIEW customers_deduped AS
SELECT
customer_id,
email,
first_name,
last_name,
company,
created_at,
source
FROM (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY created_at ASC, customer_id ASC
) AS row_num
FROM customers
) ranked
WHERE row_num = 1;
Any query that joins against or aggregates from customers_deduped will see only one row per email, automatically. This is especially useful in data warehouse environments where raw tables are immutable and transformation logic lives in views or CTEs.
Tip
If you're creating this view in a production system, put it through the same review process as application code. Views that silently deduplicate data should be clearly named (the _deduped suffix is a good convention) and documented, so the next person who uses them knows that a row-selection rule is being applied.
Not all duplicates are clean matches. Sometimes the same person is in the system twice because they spelled their name differently, used a different email alias, or filled in their company name inconsistently. These fuzzy duplicates require different detection techniques.
One practical SQL-level approach uses string normalization before grouping:
SELECT
LOWER(TRIM(email)) AS normalized_email,
LOWER(TRIM(first_name)) AS normalized_first,
LOWER(TRIM(last_name)) AS normalized_last,
COUNT(*) AS record_count
FROM customers
GROUP BY
LOWER(TRIM(email)),
LOWER(TRIM(first_name)),
LOWER(TRIM(last_name))
HAVING COUNT(*) > 1;
This catches cases where one record has "Sara Chen" and another has " sara chen " (with leading/trailing spaces). It won't catch sara.chen@acme.com vs s.chen@acme.com, but it handles a large class of real-world data entry inconsistencies.
For email-specific deduplication, you might normalize the domain:
SELECT
LOWER(TRIM(email)) AS canonical_email,
COUNT(*) AS record_count
FROM customers
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;
True fuzzy matching (Levenshtein distance, phonetic matching) generally requires application-layer processing or database extensions. SQL is good at exact and normalized matching; for the rest, flag candidates and hand them to a human or a Python script. The Master SQL String and Date Functions: Essential Data Transformation Skills lesson covers TRIM, LOWER, and related normalization functions in depth.
After any deduplication operation, verify. This is not optional.
-- Check 1: No duplicates remain
SELECT
email,
COUNT(*) AS record_count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;
-- Should return 0 rows.
-- Check 2: Total row count matches expectations
SELECT COUNT(*) AS total_rows FROM customers;
-- Check 3: Spot-check a specific record you know was a duplicate
SELECT * FROM customers WHERE email = 'sara.chen@acme.com';
-- Should return exactly 1 row, with customer_id = 1001
If Check 1 returns rows, your deletion didn't catch everything — likely because your grouping key wasn't inclusive enough. If Check 2 shows you deleted far more than expected, you may have had a logic error in your window function's PARTITION BY.
Note
Run your verification queries against a transaction-wrapped operation whenever your database supports it. Use BEGIN; DELETE ...; -- run checks -- ROLLBACK; while testing, then COMMIT only when you're satisfied. This gives you a safety net against irreversible mistakes.
Let's put all of this together in a realistic sequence you'd actually run as part of a data cleanup task.
Scenario: Your company just acquired a smaller firm and their CRM data was imported into your customers table last night. You need to identify duplicates, understand what kind they are, deduplicate safely, and report back to the data engineering team.
-- Step 1: Baseline audit
SELECT
COUNT(*) AS total_rows,
COUNT(DISTINCT email) AS unique_emails,
COUNT(*) - COUNT(DISTINCT email) AS estimated_duplicates
FROM customers;
-- Step 2: Identify all duplicate email groups with counts
SELECT
email,
COUNT(*) AS record_count,
MIN(created_at) AS first_seen,
MAX(created_at) AS last_seen,
COUNT(DISTINCT source) AS source_variety
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY record_count DESC;
-- Step 3: Pull the full rows for manual review (limit for safety)
SELECT
customer_id,
email,
first_name,
last_name,
company,
created_at,
source
FROM customers
WHERE email IN (
SELECT email
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
)
ORDER BY email, created_at
LIMIT 100;
-- Step 4: Tag rows with their rank within each duplicate group
-- (Preview only — do not delete yet)
SELECT
customer_id,
email,
created_at,
source,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY
-- Prefer webform over import; then prefer oldest record
CASE source WHEN 'webform' THEN 1 ELSE 2 END ASC,
created_at ASC,
customer_id ASC
) AS keep_rank
FROM customers
WHERE email IN (
SELECT email
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
)
ORDER BY email, keep_rank;
-- Step 5: Transaction-wrapped deletion
BEGIN;
DELETE FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM (
SELECT
customer_id,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY
CASE source WHEN 'webform' THEN 1 ELSE 2 END ASC,
created_at ASC,
customer_id ASC
) AS keep_rank
FROM customers
) ranked
WHERE keep_rank > 1
);
-- Step 6: Verify inside the transaction before committing
SELECT
COUNT(*) AS rows_after,
COUNT(DISTINCT email) AS unique_emails_after
FROM customers;
SELECT email, COUNT(*)
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;
-- If everything looks right:
COMMIT;
-- If something looks wrong:
-- ROLLBACK;
Notice the CASE source WHEN 'webform' THEN 1 ELSE 2 END ASC in the ORDER BY inside OVER(). This implements a business rule: webform submissions represent people who opted in directly and should be preserved over records that came in via bulk import, regardless of which timestamp is earlier. This kind of explicit logic in your deduplication query prevents a whole category of ambiguous decisions from being made silently by whatever record happened to get a lower ID.
Dataset: A fictional event_registrations table:
CREATE TABLE event_registrations (
registration_id INT PRIMARY KEY,
event_id INT,
attendee_email VARCHAR(255),
attendee_name VARCHAR(200),
registered_at TIMESTAMP,
ticket_type VARCHAR(50) -- 'vip', 'general', 'staff'
);
Your tasks:
Write a query that identifies all attendee_email / event_id combinations where the same person registered for the same event more than once.
Extend that query to show the total number of "extra" registrations across the entire table.
Write a ROW_NUMBER() query that ranks the registrations within each email + event_id group. Use this rule: keep vip tickets over general over staff; for ties, keep the earliest registered_at.
Write (but do not execute) the DELETE statement that would remove the duplicate registrations, keeping only the canonical row per attendee per event.
Write the verification query you would run immediately after the deletion.
Challenge: What happens to your deduplication logic if attendee_email can be NULL? How would you modify your queries to handle registrations where no email was captured?
Tip
For the NULL challenge in task 5, think about how PARTITION BY handles NULL values (all NULLs go into the same partition together) and whether that's the behavior you want. The NULL Handling in SQL: IS NULL, COALESCE, and NULLIF article covers the edge cases in detail.
Mistake 1: Deleting from the outer table while referencing it in a subquery (MySQL)
MySQL doesn't allow you to modify a table and query it in the same statement without a derived table layer. The double-nest pattern (SELECT ... FROM (SELECT ...) alias WHERE ...) is the fix. If you get an error like You can't specify target table 'customers' for update in FROM clause, add that extra layer of nesting.
Mistake 2: Forgetting to include a tiebreaker in ROW_NUMBER()
If two rows are identical in every column your ORDER BY uses, the database can assign row_num = 1 to either row non-deterministically across different executions. Always include customer_id ASC (or whatever your primary key is) as a final tiebreaker. This makes your deduplication deterministic and repeatable.
Mistake 3: Using DISTINCT instead of ROW_NUMBER() for deduplication
SELECT DISTINCT * removes rows that are identical in every column, which is rarely what you want. More commonly, you need rows that are distinct on a subset of columns (e.g., one row per email), while keeping all columns from one of the matched rows. ROW_NUMBER() is the right tool for this. If you're uncertain when to use DISTINCT versus aggregation approaches, Counting and Grouping with DISTINCT vs GROUP BY: When to Use Each and Why It Matters walks through the trade-offs.
Mistake 4: Deduplicating without checking foreign keys
If customer_id is referenced by other tables (orders, invoices, support tickets), deleting a customer row will either fail (with a foreign key constraint error) or cascade-delete a bunch of related records. Before deleting, check whether any of the duplicate IDs you're removing are referenced elsewhere. If so, update those references to point to the canonical record first.
-- Check for FK references before deleting
SELECT COUNT(*)
FROM orders
WHERE customer_id IN (
-- paste your list of duplicate customer_ids here
1003, 1005
);
If this returns rows, run an UPDATE to remap them before your DELETE:
UPDATE orders
SET customer_id = 1001
WHERE customer_id = 1003;
Mistake 5: Not scoping HAVING correctly
A query like this is a common error:
-- Broken: tries to filter on alias 'cnt' in HAVING
SELECT email, COUNT(*) AS cnt
FROM customers
GROUP BY email
HAVING cnt > 1; -- works in MySQL, fails in PostgreSQL and SQL Server
The safe, portable version always repeats the aggregate expression:
HAVING COUNT(*) > 1
Some databases allow referencing column aliases in HAVING, but it's a non-standard extension. Writing the expression directly is the correct, portable approach. Master SQL Aggregate Functions: Advanced GROUP BY, HAVING, and Performance Optimization covers these portability gotchas in more depth.
Mistake 6: Running the DELETE without a prior SELECT preview
This one doesn't need much explanation — it just needs emphasis. Every DELETE based on a subquery should be preceded by a SELECT * using the exact same WHERE clause. If the row count and content of that SELECT look right, you can proceed. If they don't, you haven't lost anything.
You now have a complete toolkit for duplicate detection and deduplication:
GROUP BY + HAVING COUNT(*) > 1: the core pattern for identifying which values have multiple rowsIN: for pulling the actual rows behind the duplicated valuesROW_NUMBER() with PARTITION BY: for tagging which row to keep and which to remove, with explicit business-rule priority baked into the ORDER BYThe most important habit this lesson should leave you with is the audit-first approach: measure the scope of the problem before touching data, define your "which row to keep" logic explicitly in code rather than relying on undefined behavior, and verify the result against your before-snapshot.
From here, a few natural next steps depending on where your work takes you:
Data quality problems like duplicates aren't glamorous, but catching and fixing them before they propagate into reports, ML training sets, or customer-facing systems is genuinely high-leverage work. These are exactly the queries that make you the person everyone trusts with production data.