ROW_NUMBER() — The Workhorse of SFMC SQL
Deduplication, latest-state selection and deterministic ranking.
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.
Use ROW_NUMBER() when the business question is “which single row should survive inside each logical group?” The hard part is not the function — it is defining the partition and ordering rules correctly.
PARTITION BY defines the business identity or group.
ORDER BY defines which row wins inside that group.
Add a deterministic tie-breaker when the primary sort can tie.
Filter rn outside the windowed SELECT, usually with rn = 1.
Do not partition by a convenient field if it is not the real business key.
Canonical pattern
The inner query ranks rows. The outer query chooses the winner. This separation keeps the logic readable and lets you inspect ranking during troubleshooting.
SELECT
SubscriberKey,
EmailAddress,
ModifiedDate
FROM (
SELECT
SubscriberKey,
EmailAddress,
ModifiedDate,
ROW_NUMBER() OVER (
PARTITION BY SubscriberKey
ORDER BY ModifiedDate DESC, RecordID DESC
) AS rn
FROM [Source_DE]
) ranked
WHERE rn = 1The partition is the business rule
PARTITION BY SubscriberKey means “one winner per subscriber”. PARTITION BY EmailAddress means “one winner per email”. Those are not interchangeable identity models.
When consolidating brands or sources, the partition key should reflect the identity rule you are actually implementing — not just whichever column happens to be populated.
Tie-breakers matter
If two rows share the same ModifiedDate, SQL is free to return either row first unless another deterministic sort key resolves the tie.
A stable record ID, source priority, created timestamp, or explicit business priority can become the second ORDER BY expression.
ROW_NUMBER() OVER (
PARTITION BY SubscriberKey
ORDER BY
ModifiedDate DESC,
CASE Source
WHEN 'MASTER' THEN 1
WHEN 'CRM' THEN 2
ELSE 9
END ASC,
RecordID DESC
) AS rnIf you cannot explain why row A wins over row B, the deduplication rule is not finished.
ROW_NUMBER is also a debugging tool
Keep rn in a temporary output while validating a rule. Rows with rn = 2, 3, 4 expose what is being discarded and often reveal unexpected duplicates or weak identity assumptions.
Keep the most recent row for each logical identity using ROW_NUMBER().
Make deduplication deterministic when the main timestamp can tie.
Use ROW_NUMBER() as a history/debugging tool, not only for rn = 1.