Two source rows share the same winning version

Illustrative lab: two product prices arrive with the same product id and source version. This is a learning case, not a reported customer incident.

Observable symptoms

A latest-row selection has no meaningful way to choose one value, or a merge fails with ambiguous matches.

Likely cause

The ordering tuple is not unique for conflicting business states.

Diagnosis steps

1. Group by business key and all version fields.
2. Separate identical retransmissions from conflicting values.
3. Ask the source owner for an ordering or conflict-resolution contract.

Example

WITH feed AS (
 SELECT column1::INTEGER id, column2::INTEGER ver, column3::NUMBER(10,2) price
 FROM VALUES (42,4,120.00), (42,4,125.00)
)
SELECT id, ver, COUNT(*) AS records, COUNT(DISTINCT price) AS prices
FROM feed GROUP BY id, ver HAVING COUNT(DISTINCT price) > 1;

Expected result

ID | VER | RECORDS | PRICES
42 | 4   | 2       | 2

Code walkthrough

• The fixture creates equal versions with different prices.
• Grouping exposes the full key/version combination.
• The HAVING condition identifies more than one non-null price. This fixture uses non-null prices; add explicit null-conflict logic when null is a valid source state.

Solution and boundaries

Quarantine conflicting equal-version records and resolve them using an agreed source rule. Add a meaningful tie-breaker only if its business semantics are known; random selection merely conceals ambiguity.

Prevention and monitoring

Validate source ordering contracts and track conflict counts before target writes.

Interview question

Why does ROW_NUMBER require more than syntactically valid ORDER BY?

Official reference · Related technical guide