sfmc // SQLPRODUCTION

Second Most Recent Record per Key

Use ROW_NUMBER() as a history/debugging tool, not only for rn = 1.

sqlrow_numberhistorydebugging

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

You need the previous state before the current winner.

You want to inspect what deduplication is discarding.

SELECT
    SubscriberKey,
    Status,
    ModifiedDate
FROM (
    SELECT
        SubscriberKey,
        Status,
        ModifiedDate,
        ROW_NUMBER() OVER (
            PARTITION BY SubscriberKey
            ORDER BY ModifiedDate DESC, RecordID DESC
        ) AS rn
    FROM [Customer_History]
) ranked
WHERE rn = 2

Why it works

The ranking gives every row a stable position inside its logical identity group.

Operational notes

Keeping rn in a temporary diagnostic output is one of the easiest ways to validate a deduplication rule.

ROW_NUMBER() with a Deterministic Tie-Breaker

Make deduplication deterministic when the main timestamp can tie.

→

Second Most Recent Record per Key

Keep following the context.