sfmc // SQLPRODUCTION

ROW_NUMBER() with a Deterministic Tie-Breaker

Make deduplication deterministic when the main timestamp can tie.

sqlrow_numberdeduptie breaker

Trust & provenancePRODUCTION

Evidence tierPRODUCTIONOfficial sources0Other sources0Last verified2026-10-05

Evidence tier describes my hands-on relationship with the content. Official sources describe platform contracts/documentation. Production observations should not automatically be treated as official limits.

EvidencePRODUCTION

Difficultyintermediate

Verified2026-10-05

Use when

Two or more rows can share the same primary sort value.

A migration or reconciliation must produce repeatable winners.

SELECT
    SubscriberKey,
    EmailAddress,
    ModifiedDate,
    RecordID
FROM (
    SELECT
        SubscriberKey,
        EmailAddress,
        ModifiedDate,
        RecordID,
        ROW_NUMBER() OVER (
            PARTITION BY SubscriberKey
            ORDER BY ModifiedDate DESC, RecordID DESC
        ) AS rn
    FROM [Source_DE]
) ranked
WHERE rn = 1

Why it works

ROW_NUMBER needs a total ordering inside each partition. A second stable sort key removes ambiguity when the primary timestamp ties.

Operational notes

The tie-breaker must be meaningful and stable; replace RecordID with a real deterministic field from your model.

Gotchas

If both ORDER BY fields can tie, the result can still be nondeterministic.

Do not use NEWID() as a tie-breaker when you need repeatable winners.

Deterministic Deduplication — Latest Record

Keep the most recent row for each logical identity using ROW_NUMBER().

→

ROW_NUMBER() with Business Priority

Choose a surviving customer row by source trust first, recency second.

→

ROW_NUMBER() with a Deterministic Tie-Breaker

Keep following the context.