Writing SQL to Rank and Deduplicate Rows Using ROW_NUMBER in a CTE: Partition, Filter, and Delete Duplicates in a Single Query

Introduction

You're three days into a data migration project. The source system had no unique constraints, and over five years of operation, the customer table accumulated thousands of duplicate records — same email address, different timestamps, sometimes slightly different names or phone numbers. Your job is to load a clean version into the new system, keeping exactly one record per customer. The question isn't whether there are duplicates; it's which duplicate to keep and how to delete the rest without destroying anything important.

This is the scenario that makes ROW_NUMBER() inside a CTE one of the most powerful patterns in a SQL practitioner's toolkit. The technique is elegant: assign a rank to each row within a group of duplicates, then filter to keep only rank 1 — or delete everything that isn't rank 1. What makes it genuinely useful in production is the control it gives you. You decide what makes rows duplicates (the partition), you decide which one to keep (the ordering), and you can inspect the results before you delete a single byte.

By the end of this lesson, you'll know how to build this pattern from scratch, understand why it works at the query-plan level, handle the edge cases that trip people up in production, and have a reusable template you can drop into any deduplication job.

What you'll learn:

• How ROW_NUMBER() with PARTITION BY assigns ranks within duplicate groups
• How to wrap that logic in a CTE and filter to identify rows to keep or discard
• How to safely DELETE duplicate rows by referencing a CTE directly
• How to tune this pattern for large tables — index strategy, temp tables, and batching
• The key differences in syntax and behavior across PostgreSQL, SQL Server, and MySQL

---

Prerequisites

This lesson assumes you're comfortable with basic SELECT, JOIN, and GROUP BY queries, and that you have some exposure to window functions and CTEs — even if you haven't used them in anger yet. If you need a refresher on how ROW_NUMBER() fits into the broader family of window functions, Window Functions: RANK, ROW_NUMBER, and LAG covers the fundamentals well. If CTEs are new to you, Common Table Expressions (CTEs) for Cleaner SQL is a solid starting point before you continue here.

---

Understanding the Core Pattern

Before we write a single DELETE, let's make sure we understand exactly what ROW_NUMBER() is doing and why it's the right tool here instead of RANK() or DENSE_RANK().

ROW_NUMBER() assigns a unique sequential integer to every row within a partition. It never ties — even if two rows are completely identical, they get different numbers. RANK() and DENSE_RANK() will assign the same number to ties, which means you could end up keeping all of your duplicates if they're perfect matches. For deduplication, you want exactly one row per group to get the number 1, so ROW_NUMBER() is the correct choice.

Here's the basic anatomy:

ROW_NUMBER() OVER (
    PARTITION BY column_or_columns_that_define_a_duplicate
    ORDER BY column_that_decides_which_row_to_keep
)

The PARTITION BY clause is where you define what "duplicate" means. Two rows are duplicates of each other when they share the same values across all the columns you specify here. The ORDER BY clause is where you decide priority — which row within that group gets rank 1. The lowest created_at timestamp keeps the original record; the highest keeps the most recent one; a status column value could let you prefer active accounts over cancelled ones.

Let's build a realistic table to work with throughout this lesson.

-- A customer table with duplicates introduced by a bad ETL process
CREATE TABLE customers (
    id          SERIAL PRIMARY KEY,
    email       VARCHAR(255),
    full_name   VARCHAR(255),
    phone       VARCHAR(20),
    status      VARCHAR(20),
    created_at  TIMESTAMP,
    source      VARCHAR(50)
);

INSERT INTO customers (email, full_name, phone, status, created_at, source) VALUES
('alice@example.com',   'Alice Johnson',  '555-0101', 'active',    '2021-03-15 09:00:00', 'web'),
('alice@example.com',   'Alice M Johnson','555-0101', 'active',    '2022-07-20 14:30:00', 'crm_import'),
('alice@example.com',   'Alice Johnson',  '555-0101', 'cancelled', '2023-01-10 11:00:00', 'crm_import'),
('bob@example.com',     'Bob Williams',   '555-0202', 'active',    '2020-11-05 08:15:00', 'web'),
('bob@example.com',     'Bob Williams',   '555-0202', 'active',    '2020-11-05 08:15:00', 'web'),
('carol@example.com',   'Carol Davis',    '555-0303', 'active',    '2022-05-01 16:00:00', 'api'),
('dave@example.com',    'Dave Martinez',  '555-0404', 'inactive',  '2021-09-12 10:00:00', 'web'),
('dave@example.com',    'David Martinez', '555-0404', 'active',    '2022-02-28 09:00:00', 'crm_import');

Notice a few different flavors of "duplicate" in this data:

• Alice has three records with the same email, but different names and statuses — a real-world mess from multiple imports
• Bob has two records that are genuinely identical (same timestamp, same source) — a classic ETL double-load
• Carol has no duplicate — she should be untouched
• Dave has two records with slightly different names and statuses — a data quality issue from manual entry

---

Step 1: Assign Row Numbers to Identify Duplicates

The first step is always to run the ranking query on its own and inspect the results before you do anything destructive. This is non-negotiable in production.

SELECT
    id,
    email,
    full_name,
    status,
    created_at,
    source,
    ROW_NUMBER() OVER (
        PARTITION BY email
        ORDER BY
            CASE status WHEN 'active' THEN 1
                        WHEN 'inactive' THEN 2
                        ELSE 3
            END,
            created_at ASC
    ) AS rn
FROM customers
ORDER BY email, rn;

This query partitions by email — our definition of "duplicate" — and orders within each group by status priority first (preferring active accounts), then by created_at ascending as a tiebreaker to keep the earliest record when statuses are equal.

The result looks like this:

id | email              | full_name       | status    | created_at          | rn
---+--------------------+-----------------+-----------+---------------------+----
 1 | alice@example.com  | Alice Johnson   | active    | 2021-03-15 09:00:00 |  1  ← KEEP
 2 | alice@example.com  | Alice M Johnson | active    | 2022-07-20 14:30:00 |  2
 3 | alice@example.com  | Alice Johnson   | cancelled | 2023-01-10 11:00:00 |  3
 4 | bob@example.com    | Bob Williams    | active    | 2020-11-05 08:15:00 |  1  ← KEEP
 5 | bob@example.com    | Bob Williams    | active    | 2020-11-05 08:15:00 |  2
 6 | carol@example.com  | Carol Davis     | active    | 2022-05-01 16:00:00 |  1  ← KEEP (no dupe)
 7 | dave@example.com   | Dave Martinez   | inactive  | 2021-09-12 10:00:00 |  2
 8 | dave@example.com   | David Martinez  | active    | 2022-02-28 09:00:00 |  1  ← KEEP

---

Step 2: Wrap It in a CTE to Filter

Now we wrap that query in a CTE and filter to see only the rows we want to delete:

WITH ranked_customers AS (
    SELECT
        id,
        email,
        full_name,
        status,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY
                CASE status WHEN 'active' THEN 1
                            WHEN 'inactive' THEN 2
                            ELSE 3
                END,
                created_at ASC
        ) AS rn
    FROM customers
)
SELECT id, email, full_name, status, created_at
FROM ranked_customers
WHERE rn > 1;

This returns exactly the rows that are duplicates — the ones that should be deleted:

id | email             | full_name       | status    | created_at
---+-------------------+-----------------+-----------+---------------------
 2 | alice@example.com | Alice M Johnson | active    | 2022-07-20 14:30:00
 3 | alice@example.com | Alice Johnson   | cancelled | 2023-01-10 11:00:00
 5 | bob@example.com   | Bob Williams    | active    | 2020-11-05 08:15:00
 7 | dave@example.com  | Dave Martinez   | inactive  | 2021-09-12 10:00:00

This is your deduplication audit log. Before you run a DELETE, consider saving this result to a backup table. That's cheap insurance.

-- Save duplicates to an audit table before deleting
CREATE TABLE customers_duplicates_audit AS
WITH ranked_customers AS (
    SELECT
        id,
        email,
        full_name,
        status,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY
                CASE status WHEN 'active' THEN 1
                            WHEN 'inactive' THEN 2
                            ELSE 3
                END,
                created_at ASC
        ) AS rn
    FROM customers
)
SELECT id, email, full_name, status, created_at, NOW() AS archived_at
FROM ranked_customers
WHERE rn > 1;

---

Step 3: Delete the Duplicates — Database-Specific Syntax

This is where the databases diverge, and knowing the differences matters.

PostgreSQL

PostgreSQL allows you to reference a CTE directly in a DELETE statement using its WITH clause. This is clean and idiomatic:

WITH ranked_customers AS (
    SELECT
        id,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY
                CASE status WHEN 'active' THEN 1
                            WHEN 'inactive' THEN 2
                            ELSE 3
                END,
                created_at ASC
        ) AS rn
    FROM customers
)
DELETE FROM customers
WHERE id IN (
    SELECT id FROM ranked_customers WHERE rn > 1
);

PostgreSQL also supports a more direct syntax using DELETE ... USING:

WITH ranked_customers AS (
    SELECT
        id,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY
                CASE status WHEN 'active' THEN 1
                            WHEN 'inactive' THEN 2
                            ELSE 3
                END,
                created_at ASC
        ) AS rn
    FROM customers
)
DELETE FROM customers c
USING ranked_customers rc
WHERE c.id = rc.id
  AND rc.rn > 1;

The USING syntax is often more efficient because it avoids a subquery inside the WHERE clause — the query planner can join directly.

SQL Server

SQL Server supports something particularly elegant: you can DELETE from a CTE directly. The CTE essentially acts as an updateable view:

WITH ranked_customers AS (
    SELECT
        id,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY
                CASE status WHEN 'active' THEN 1
                            WHEN 'inactive' THEN 2
                            ELSE 3
                END,
                created_at ASC
        ) AS rn
    FROM customers
)
DELETE FROM ranked_customers
WHERE rn > 1;

This is the most concise form and is widely used in SQL Server shops. It works because SQL Server can trace the CTE rows back to their source table rows unambiguously — each row in the CTE maps to exactly one row in customers via the primary key.

MySQL

MySQL (pre-8.0) doesn't support CTEs at all. MySQL 8.0 added CTE support, but doesn't allow modifying a table that's referenced in the FROM clause of the same statement — a restriction that blocks the most direct approach. The workaround is a subquery:

-- MySQL 8.0+
DELETE FROM customers
WHERE id IN (
    SELECT id FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (
                PARTITION BY email
                ORDER BY
                    CASE status WHEN 'active' THEN 1
                                WHEN 'inactive' THEN 2
                                ELSE 3
                    END,
                    created_at ASC
            ) AS rn
        FROM customers
    ) subq
    WHERE subq.rn > 1
);

The double-subquery is an unfortunate necessity — the outer subquery wraps the inner one to satisfy MySQL's restriction on self-referencing in modifications.

---

Step 4: Building a Production-Ready Deduplication Script

A single-shot DELETE on a large table can cause problems: long lock times, transaction log bloat, and replication lag. Let's build a more production-friendly version that batches the deletes, wraps them in a transaction with a safety check, and logs what it did.

-- Full production deduplication script (PostgreSQL)
-- Step 1: Preview scope
WITH ranked_customers AS (
    SELECT
        id,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY
                CASE status WHEN 'active' THEN 1
                            WHEN 'inactive' THEN 2
                            ELSE 3
                END,
                created_at ASC
        ) AS rn
    FROM customers
)
SELECT
    COUNT(*)                                        AS total_rows,
    COUNT(*) FILTER (WHERE rn = 1)                  AS rows_to_keep,
    COUNT(*) FILTER (WHERE rn > 1)                  AS rows_to_delete,
    COUNT(DISTINCT email)                           AS unique_emails
FROM ranked_customers;

Run this first. It tells you the scope of the operation before you commit to anything.

total_rows | rows_to_keep | rows_to_delete | unique_emails
-----------+--------------+----------------+--------------
         8 |            4 |              4 |             4

Now the transactional delete with a rollback guard:

BEGIN;

-- Archive duplicates first
CREATE TEMP TABLE customers_to_delete AS
WITH ranked_customers AS (
    SELECT
        id,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY
                CASE status WHEN 'active' THEN 1
                            WHEN 'inactive' THEN 2
                            ELSE 3
                END,
                created_at ASC
        ) AS rn
    FROM customers
)
SELECT id
FROM ranked_customers
WHERE rn > 1;

-- Verify the count matches your preview
DO $$
DECLARE
    v_count INT;
BEGIN
    SELECT COUNT(*) INTO v_count FROM customers_to_delete;
    IF v_count != 4 THEN
        RAISE EXCEPTION 'Expected 4 rows to delete, found %. Aborting.', v_count;
    END IF;
END;
$$;

-- Perform the delete
DELETE FROM customers
WHERE id IN (SELECT id FROM customers_to_delete);

-- Verify post-delete state
DO $$
DECLARE
    v_remaining INT;
BEGIN
    SELECT COUNT(*) INTO v_remaining
    FROM (
        SELECT email, COUNT(*) AS cnt
        FROM customers
        GROUP BY email
        HAVING COUNT(*) > 1
    ) dupes;

    IF v_remaining > 0 THEN
        RAISE EXCEPTION 'Duplicates still exist after delete. Rolling back.';
    END IF;
END;
$$;

COMMIT;

This script uses a temp table to materialize the IDs to delete (so the ranking is computed once), validates the expected count before deleting, and validates the result after deleting. If either check fails, the exception triggers an automatic rollback. For more on how transactions and locking work in this context, see SQL Transactions, Isolation Levels, and Locking.

---

Handling Complex Duplicate Definitions

Real-world deduplication is rarely as clean as "same email = duplicate." Let's look at a few more complex scenarios.

Composite Key Duplicates

A sales order table where duplicates are defined by customer ID + product ID + order date (the same customer shouldn't have two orders for the same product on the same day):

WITH ranked_orders AS (
    SELECT
        order_id,
        customer_id,
        product_id,
        order_date,
        quantity,
        unit_price,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id, product_id, order_date
            ORDER BY
                -- Keep the order with the higher quantity (assume it's the correction)
                quantity DESC,
                -- Then by order_id DESC as final tiebreaker (most recent entry)
                order_id DESC
        ) AS rn
    FROM sales_orders
)
DELETE FROM sales_orders
WHERE order_id IN (
    SELECT order_id FROM ranked_orders WHERE rn > 1
);

Duplicates Across Normalized Tables

Sometimes you need to deduplicate a child table while respecting referential integrity. Suppose order_items references orders, and you want to keep the most recent version of each item:

WITH ranked_items AS (
    SELECT
        item_id,
        order_id,
        product_id,
        ROW_NUMBER() OVER (
            PARTITION BY order_id, product_id
            ORDER BY item_id DESC  -- highest ID = most recently inserted
        ) AS rn
    FROM order_items
)
-- First, update foreign key references in dependent tables to point to the keeper row
-- Then delete
DELETE FROM order_items
WHERE item_id IN (
    SELECT item_id FROM ranked_items WHERE rn > 1
);

NULL Values in Partition Columns

If any of your partition columns can be NULL, PARTITION BY treats NULLs as a group — all rows with NULL in that column are partitioned together. This is usually not what you want. Handle it explicitly:

WITH ranked_customers AS (
    SELECT
        id,
        email,
        ROW_NUMBER() OVER (
            PARTITION BY COALESCE(email, 'NO_EMAIL_' || id::text)
            ORDER BY created_at ASC
        ) AS rn
    FROM customers
)
SELECT * FROM ranked_customers WHERE rn > 1;

By generating a unique synthetic key for NULL emails, you ensure that records with no email are never grouped together — each is treated as its own entity. For more on working with NULLs reliably, see Understanding SQL NULL Handling: COALESCE, NULLIF, and IS NULL.

---

Performance Considerations

This pattern can be expensive on large tables. Here's what you need to know.

Index Strategy

The window function's PARTITION BY and ORDER BY columns should be indexed. For our customer example:

-- A composite index supporting the partition and sort
CREATE INDEX idx_customers_email_status_created
ON customers (email, status, created_at);

This allows the database to perform a sorted scan by email (the partition key), which is dramatically cheaper than sorting the entire table. Without this index, the query plan will show a full table scan followed by a sort — on a 10 million row table, that's the difference between seconds and minutes. For a deep dive on building the right indexes for queries like this, see Indexing Fundamentals for Query Performance.

Batched Deletes for Large Datasets

Deleting millions of rows in a single transaction locks those rows for the entire duration, generates massive transaction log entries, and can cause replication lag. Batch it:

-- PostgreSQL batched delete loop
DO $$
DECLARE
    v_deleted INT;
    v_total   INT := 0;
BEGIN
    LOOP
        WITH ranked_customers AS (
            SELECT
                id,
                ROW_NUMBER() OVER (
                    PARTITION BY email
                    ORDER BY
                        CASE status WHEN 'active' THEN 1
                                    WHEN 'inactive' THEN 2
                                    ELSE 3
                        END,
                        created_at ASC
                ) AS rn
            FROM customers
        ),
        to_delete AS (
            SELECT id FROM ranked_customers WHERE rn > 1 LIMIT 10000
        )
        DELETE FROM customers
        WHERE id IN (SELECT id FROM to_delete);

        GET DIAGNOSTICS v_deleted = ROW_COUNT;
        v_total := v_total + v_deleted;

        RAISE NOTICE 'Deleted % rows (total: %)', v_deleted, v_total;

        EXIT WHEN v_deleted = 0;

        PERFORM pg_sleep(0.1); -- brief pause to let replication catch up
    END LOOP;

    RAISE NOTICE 'Deduplication complete. Total deleted: %', v_total;
END;
$$;

Each iteration deletes up to 10,000 rows and commits before moving to the next batch. The brief sleep gives replicas a chance to catch up. The window function re-executes each iteration, which is slightly wasteful but ensures correctness — deleted rows don't appear in subsequent iterations.

Reading the Execution Plan

When you're troubleshooting performance, check the query plan:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
WITH ranked_customers AS (
    SELECT
        id,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY created_at ASC
        ) AS rn
    FROM customers
)
SELECT id FROM ranked_customers WHERE rn > 1;

You're looking for:

• Index Scan on the partition/sort columns — good
• Sort node with high estimated rows — a warning sign that your index isn't being used
• WindowAgg — this is the window function execution, expected
• Filter applied after WindowAgg — also expected; the rn > 1 filter can't be pushed below the window function

If you see a Hash Join or Nested Loop on a large table where you expect an Index Scan, your indexes may need adjustment. For a thorough treatment of reading and acting on execution plans, see SQL Query Optimization: Reading Execution Plans — Advanced Performance Analysis.

---

Hands-On Exercise

Time to apply this yourself. Use the following dataset — a subscription events table from a SaaS application that accumulated duplicates during a billing system migration:

CREATE TABLE subscription_events (
    event_id        SERIAL PRIMARY KEY,
    account_id      INT,
    event_type      VARCHAR(50),   -- 'signup', 'upgrade', 'downgrade', 'cancel'
    plan_name       VARCHAR(50),
    event_timestamp TIMESTAMP,
    processed_by    VARCHAR(50)    -- 'billing_v1', 'billing_v2', 'manual'
);

INSERT INTO subscription_events
    (account_id, event_type, plan_name, event_timestamp, processed_by)
VALUES
    (1001, 'signup',    'starter',      '2023-01-15 09:00:00', 'billing_v1'),
    (1001, 'signup',    'starter',      '2023-01-15 09:00:00', 'billing_v1'),  -- exact dupe
    (1001, 'upgrade',   'professional', '2023-06-01 14:00:00', 'billing_v2'),
    (1001, 'upgrade',   'professional', '2023-06-01 14:05:00', 'billing_v2'),  -- near-dupe, 5 min later
    (1002, 'signup',    'professional', '2023-02-20 10:00:00', 'billing_v1'),
    (1002, 'cancel',    'professional', '2023-11-30 16:00:00', 'billing_v2'),
    (1002, 'cancel',    'professional', '2023-11-30 16:00:00', 'manual'),       -- same time, different processor
    (1003, 'signup',    'enterprise',   '2023-03-10 08:00:00', 'billing_v2'),
    (1004, 'signup',    'starter',      '2023-04-05 11:00:00', 'billing_v1'),
    (1004, 'upgrade',   'professional', '2023-07-20 09:00:00', 'billing_v1'),
    (1004, 'upgrade',   'professional', '2023-07-20 09:00:00', 'billing_v2');  -- same event, different processor

Your tasks:

1. Identify duplicates: Write a CTE with ROW_NUMBER() that partitions by account_id and event_type, ordering to keep the event processed by billing_v2 over billing_v1, and by earliest timestamp as a tiebreaker. Run it as a SELECT to verify your results.

2. Audit before delete: Save the rows to be deleted into an audit table called subscription_events_audit.

3. Execute the delete: Write the DELETE statement that removes all rows with rn > 1.

4. Verify the result: Write a query that confirms no account+event_type combination has more than one row remaining.

5. Stretch goal: Account 1001 had an upgrade at 14:00 and another at 14:05 — these might be legitimately different events, not duplicates. Modify your deduplication logic to treat events as duplicates only if they occurred within 1 minute of each other for the same account and event type.

---

Common Mistakes & Troubleshooting

Mistake 1: Forgetting That rn > 1 Keeps Duplicates Within Partitions, Not Across All Rows

It's easy to accidentally write WHERE rn = 1 and think you're deleting duplicates when you're actually selecting the rows to keep. Double-check your WHERE clause direction: rn > 1 means "this row is a duplicate" and should be deleted. rn = 1 means "this row is the winner" and should be kept.

Mistake 2: Wrong PARTITION BY Columns

If your partition is too narrow (e.g., just email when duplicates are really defined by email + account_type), you'll delete legitimate records. If it's too broad (e.g., email, full_name when names have slight variations), duplicates won't be grouped and nothing gets deleted. Always verify your partition logic against known examples before running.

Mistake 3: Ordering by a Non-Deterministic Column

If your ORDER BY within the window function doesn't produce a stable sort — for example, ordering only by created_at when multiple rows have identical timestamps — the database is free to assign rn = 1 to any of the tied rows. This is non-deterministic. Add a tiebreaker like the primary key (id ASC or id DESC) to guarantee determinism:

ROW_NUMBER() OVER (
    PARTITION BY email
    ORDER BY created_at ASC, id ASC  -- id as tiebreaker
)

Mistake 4: Assuming CTEs Are Always Materialized

In PostgreSQL prior to version 12, CTEs were always materialized (executed once, results stored). From PostgreSQL 12 onwards, the optimizer can inline a CTE, potentially executing it multiple times. This matters for the DELETE pattern — if the CTE is re-executed during the DELETE, you might get a different assignment of row numbers due to concurrent inserts. Use WITH ... AS MATERIALIZED explicitly if you need guaranteed single execution:

WITH ranked_customers AS MATERIALIZED (
    SELECT id, ROW_NUMBER() OVER (...) AS rn
    FROM customers
)
DELETE FROM customers
WHERE id IN (SELECT id FROM ranked_customers WHERE rn > 1);

Mistake 5: Running on a Live Table Without a Transaction

A DELETE without a transaction means there's no rollback if something goes wrong. Always wrap your deduplication in BEGIN / COMMIT and test with ROLLBACK before you commit for real:

BEGIN;

WITH ranked_customers AS (...)
DELETE FROM customers WHERE id IN (...);

-- Check the result looks right
SELECT COUNT(*) FROM customers;

-- If happy: COMMIT;
-- If not:   ROLLBACK;
ROLLBACK; -- Comment this out and uncomment COMMIT when ready

Mistake 6: Duplicate-Based Deduplication vs. Near-Duplicate Deduplication

ROW_NUMBER() only handles exact matches on your partition columns. If your duplicates have slight variations (different spellings, typos, whitespace differences), you need fuzzy matching techniques. That's a different problem entirely — see Fuzzy Matching and Deduplication in SQL: Using Similarity Functions and Blocking Strategies to Find Near-Duplicate Records for how to approach it.

---

Alternative Approaches Worth Knowing

The ROW_NUMBER() CTE pattern is the most flexible approach, but it's worth knowing what alternatives exist and when they might be appropriate.

Self-join with MIN(id): An older pattern that works in databases without window function support — keep the row with the minimum id per group, delete the rest. It's simpler to read but less flexible (you can't apply complex ordering logic to decide which row to keep).

DELETE FROM customers
WHERE id NOT IN (
    SELECT MIN(id)
    FROM customers
    GROUP BY email
);

This keeps the row with the lowest id, which may not be the row you actually want. The ROW_NUMBER() approach wins whenever your "which row to keep" logic is more complex than "lowest ID." You might also consider Mastering SQL Set Operations: UNION, INTERSECT, and EXCEPT for cases where you're reconciling duplicates across two separate tables rather than within one.

DISTINCT or GROUP BY into a new table: Create a clean version of the table, drop the original, rename the new one. Faster for massive tables where row-level deletes are too slow, but it requires downtime or careful handling of concurrent writes.

CREATE TABLE customers_clean AS
SELECT DISTINCT ON (email) *
FROM customers
ORDER BY email, created_at ASC;
-- Then swap tables

The DISTINCT ON syntax is PostgreSQL-specific and extremely efficient. It's worth knowing for PostgreSQL environments where you're doing a full rebuild rather than an in-place cleanup.

---

Summary & Next Steps

The ROW_NUMBER() + CTE deduplication pattern is a cornerstone technique for data quality work. Here's the mental model to take away:

1. Define "duplicate" via PARTITION BY — be precise about what makes two rows the same thing
2. Define "winner" via ORDER BY — be explicit and deterministic about which row to keep
3. Preview before deleting — always run as SELECT first, audit to a backup table
4. Delete with safety rails — transactions, row count checks, post-delete validation
5. Tune for scale — materialize IDs to a temp table, batch large deletes, index your partition and sort columns

The technique extends naturally beyond simple deduplication. The same ROW_NUMBER() + CTE pattern underlies slowly changing dimension management (keeping only the current version of a record), session boundary detection in event streams, and "latest record per group" queries that show up constantly in analytics work. If you're working with time-series data or historical records, Temporal Data Mastery: Writing Queries for Time-Series, Date Ranges, and Slowly Changing Dimensions will show you how this pattern integrates into those workflows.

For next steps in building out your window function expertise, Advanced Window Frame Specifications: ROWS, RANGE, and GROUPS Clauses for Precise Rolling Calculations goes deep on controlling exactly which rows participate in each window calculation — essential for running totals, moving averages, and more complex analytical patterns.