SQL deduplication and latest-row problems: keeping the right record

Repeated rows are not always duplicates

Seeing the same customer several times in a table does not automatically mean the data contains duplicates. Consider a table that records changes to customer accounts over time:

Asha, Ben, and Carla each appear twice, but these rows represent separate updates. For Asha, July 1 -> active and July 10 -> premium are both meaningful. So a repeated customer_id does not automatically mean we should remove one of the rows.

Different meanings of duplicate

The word duplicate can describe several different problems, and they should not automatically be solved with the same SQL:

• Exact duplicate rows: two rows contain the same values for everything we care about.
• Repeated entities: the same customer appears several times because their state changed over time.
• Duplicate events from retries: the same business event may have been ingested more than once.
• Latest-state problems: several historical records are valid, but the output requires only the most recent one.

A good deduplication query starts by defining which columns identify the thing that should be unique, and, if several rows represent that thing, which one should survive.

Removing duplicate result rows with DISTINCT

Suppose the requirement is show every unique status that has been observed for each customer. We do not care when the status occurred:

SELECT DISTINCT
    customer_id,
    customer_name,
    status
FROM customer_updates
ORDER BY
    customer_id,
    status;

Ben had two active updates (203 and 204). Because the selected values are identical (Ben | active), DISTINCT returns that combination once. This is correct because the requirement asks for unique customer-status combinations.

DISTINCT does not mean one row per customer

Now suppose the requirement is return one row per customer showing their current status. Someone tries the same SELECT DISTINCT customer_id, customer_name, status. Asha still appears twice (active and premium), and so does Carla. DISTINCT is working correctly; those are different result rows. The real requirement is not “remove identical rows,” it is “choose one record from several records belonging to the same customer.” That is a different problem.

Latest row per customer

Suppose the requirement is return the most recent update for each customer. We need exactly one row per customer_id, and when several rows belong to the same customer, we need the latest one. This is a Top-1-per-group problem, solved with ROW_NUMBER(). First rank each customer’s rows:

SELECT
    update_id,
    customer_id,
    customer_name,
    updated_at,
    status,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY updated_at DESC
    ) AS rn
FROM customer_updates;

SQL creates an independent ranking for every customer. For Asha, 202 (July 10, premium) gets rn = 1 and 201 (July 1, active) gets rn = 2. For Ben, 204 (July 20) gets rn = 1. The newest record gets rn = 1. Window-function results are commonly filtered in an outer query or CTE:

WITH ranked_updates AS (
    SELECT
        update_id,
        customer_id,
        customer_name,
        updated_at,
        status,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY updated_at DESC
        ) AS rn
    FROM customer_updates
)
SELECT
    update_id,
    customer_id,
    customer_name,
    updated_at,
    status
FROM ranked_updates
WHERE rn = 1
ORDER BY customer_id;

PARTITION BY customer_id  Rank each customer's records separately.
ORDER BY updated_at DESC  Newest record first.
ROW_NUMBER()              Give every record a unique position.
WHERE rn = 1              Keep the first record from each customer.

This pattern is widely useful for latest customer status, most recent account balance, latest product price, current address, latest device reading, and most recent application state.

The latest-row query with an unresolved tie

Look closely at Carla’s records: 205 (pending) and 206 (active) both have exactly the same updated_at of 2026-07-05 08:00:00. Now consider ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC). SQL knows both records are newer than anything else in Carla’s partition, but it does not know which tied row should come first.

ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY updated_at DESC
)

-- Carla:
-- 205 | 2026-07-05 08:00:00 | pending
-- 206 | 2026-07-05 08:00:00 | active

-- A: pending    B: active    C: neither is guaranteed    D: both receive rn = 1

The fix: add a deterministic tie-breaker

Suppose the data contract tells us: if two updates have the same timestamp, the larger update_id represents the later accepted update. Then order by updated_at DESC, update_id DESC, so Carla’s 206 (active) gets rn = 1 and 205 (pending) gets rn = 2:

WITH ranked_updates AS (
    SELECT
        update_id,
        customer_id,
        customer_name,
        updated_at,
        status,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY
                updated_at DESC,
                update_id DESC
        ) AS rn
    FROM customer_updates
)
SELECT
    update_id,
    customer_id,
    customer_name,
    updated_at,
    status
FROM ranked_updates
WHERE rn = 1
ORDER BY customer_id;

The important point is not that update_id DESC is always the correct tie-breaker. It is correct only because we defined that business rule. Your system might instead use ingested_at, version_number, event_sequence, source_priority, or transaction_id. The tie-breaker should come from the meaning of the data.

Latest timestamp is not always enough

A common approach is to first calculate MAX(updated_at) per customer with GROUP BY customer_id. This correctly finds the latest timestamp for each customer, but it gives us only customer_id and the latest timestamp; it does not automatically give us status, customer_name, or update_id. So someone may join the result back:

WITH latest_times AS (
    SELECT
        customer_id,
        MAX(updated_at) AS latest_updated_at
    FROM customer_updates
    GROUP BY customer_id
)
SELECT
    u.*
FROM customer_updates AS u
JOIN latest_times AS l
    ON l.customer_id = u.customer_id
   AND l.latest_updated_at = u.updated_at;

This is an important SQL distinction. MAX(updated_at) answers “what is the largest timestamp?” It does not answer “which complete row should represent the latest customer state?” If several rows share that timestamp, MAX cannot choose between them. This is why ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC, update_id DESC) is often a natural solution when exactly one complete row must be chosen.

ROW_NUMBER vs RANK for deduplication

Suppose two latest records tie. ROW_NUMBER() still assigns unique numbers (1, 2), so WHERE rn = 1 returns exactly one row per customer. RANK() can assign 1, 1 to tied rows, so WHERE rank = 1 may return more than one row for a customer. That can be correct when the requirement is “return every record tied for latest,” but wrong when it is “return exactly one current record per customer.”

Exactly one row needed   ->  ROW_NUMBER with deterministic ordering
All tied winners needed  ->  RANK may be appropriate

Removing retry duplicates

Another common deduplication problem appears in event pipelines. Suppose an upstream system retries the same update and produces two rows, 301 and 302, both customer_id 1, 2026-07-10 12:00, premium. If the business has established that these represent the same logical update, we need to define the business key for that event, for example customer_id, updated_at, status. Then:

WITH deduplicated AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY
                customer_id,
                updated_at,
                status
            ORDER BY update_id
        ) AS rn
    FROM customer_updates
)
SELECT
    *
FROM deduplicated
WHERE rn = 1;

Here the partition means “rows representing the same logical event,” and the ordering means “which physical copy should survive.”

The deduplication key comes from the business

Do not automatically decide that customer_id is the deduplication key. For historical customer updates, one customer legitimately has many rows. Similarly, order_id might identify one logical order, but an order-history table may legitimately contain several versions of that order. The correct uniqueness rule might be customer_id + event_type + event_time, order_id + version, or source_system + event_id.

Deduplication vs latest-state selection

These are related but not identical. Deduplication asks “are these two rows copies of the same logical record?” (for example retry 1 and retry 2), and we usually keep one copy. Latest-state selection asks “several historical rows are valid; which one represents the current state?” (July 1 -> active, July 10 -> premium); neither row is a duplicate, but a current-state report needs only premium. Do not describe all repeated entities as duplicates; historical records can be legitimate while still requiring one latest row in a particular query.

Keeping the earliest row instead

The same pattern works when the requirement asks for the first record. For “return each customer’s first known status,” change the ordering to ORDER BY updated_at ASC, update_id ASC and keep WHERE rn = 1. The pattern is not inherently about “latest.” It is: rank rows within each entity according to the business priority, then keep the required position.

Latest record under an additional condition

Suppose the requirement is “return each customer’s latest active or premium update.” This is not necessarily the same as “find every customer’s latest update, then keep it only if it is active or premium.” The placement of the condition matters. Filtering eligible records before ranking (WHERE status IN ('active', 'premium') inside the CTE) asks “among each customer’s active or premium records, which is latest?” Ranking everything first and filtering after (WHERE rn = 1 AND status IN ('active', 'premium')) asks “what is each customer’s latest record, and is that latest record active or premium?” Those are different questions, and the correct placement comes from the requirement.

How to spot deduplication and latest-row logic

Think about deduplication or latest-row logic when the requirement says one row per customer, latest record per account, current status, most recent price, latest event per device, remove duplicate ingestion retries, keep one copy of each logical event, first event per user, or current version of each record.

Watch for these common mistakes:

• DISTINCT is used when rows differ and one must be chosen.
• MAX(timestamp) finds a time but not necessarily one unique row.
• A join back to MAX(timestamp) returns several tied records.
• ROW_NUMBER orders only by a timestamp that can tie.
• A tie-breaker is chosen arbitrarily rather than from the business rules.
• RANK() is used even though exactly one row must survive.
• The deduplication key is too broad and removes valid history.
• The deduplication key is too narrow and fails to collapse real retries.
• Filtering before ranking changes which rows are eligible to become latest.

Answer four questions for every deduplication problem

One more prediction

Consider Carla’s records. The business rule says: return exactly one latest record per customer, and if timestamps tie, the larger update_id wins. Which expression correctly ranks Carla’s rows?

-- Option A
ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY updated_at DESC)

-- Option B
RANK() OVER (
    PARTITION BY customer_id
    ORDER BY updated_at DESC)

-- Option C
ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY updated_at DESC, update_id DESC)

-- Option D
ROW_NUMBER() OVER (
    ORDER BY updated_at DESC, update_id DESC)

Summary

Deduplication is not simply “remove repeated-looking rows.” The first question is what one result row should represent. DISTINCT works when the requirement is genuinely about unique output combinations, but latest-row problems require choosing one row from several valid records, commonly with ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC, update_id DESC) filtered to rn = 1. The important pieces are PARTITION BY (what entity should have one result row), ORDER BY (which record should win), the tie-breaker (what happens when the primary ordering value ties), ROW_NUMBER (give every candidate a unique position), and rn = 1 (keep the winner).

Next, we will move to gaps, islands, and streaks, where analytical SQL is used to identify consecutive sequences, breaks between events, and continuous periods of activity.