Make Latest-Row Queries Deterministic in DuckDB

Version scope: DuckDB 2.0. The SQL in this article was run on DuckDB 2.0.0 against small synthetic fixtures.

“Keep the latest row” sounds precise until two events have the same timestamp. A retry may duplicate an event. A delayed message may arrive after a newer change. A delete may be the most recent state. The SQL needs a business ordering rule before it needs a window function.

For this example, each event belongs to an entity, carries an event timestamp and has a unique source sequence within that entity. Larger sequence values break timestamp ties. This is a declared application contract, not a property that can be inferred from arbitrary identifiers.

DuckDB provides window functions such as row_number that can number rows within ordered partitions. The ordering keys must express the intended winner. DuckDB window functions

Build a fixture that contains the ambiguity

This illustrative lab uses synthetic data and no external files.

CREATE TEMP TABLE changes (
  entity_id INTEGER,
  changed_at TIMESTAMP,
  source_seq INTEGER,
  operation VARCHAR,
  label VARCHAR
);
INSERT INTO changes VALUES
  (7, TIMESTAMP '2026-10-01 10:00:00', 1, 'upsert', 'basic'),
  (7, TIMESTAMP '2026-10-01 10:00:00', 2, 'upsert', 'pro'),
  (8, TIMESTAMP '2026-10-01 11:00:00', 1, 'upsert', 'basic'),
  (8, TIMESTAMP '2026-10-01 12:00:00', 2, 'delete', NULL);

WITH ranked AS (
  SELECT *, row_number() OVER (
    PARTITION BY entity_id
    ORDER BY changed_at DESC, source_seq DESC
  ) AS position
  FROM changes
)
SELECT entity_id, label
FROM ranked
WHERE position = 1 AND operation <> 'delete'
ORDER BY entity_id;

Under the stated contract, the expected surviving entity is 7 with label pro. Entity 8 is absent because its latest event is a delete. Running the lab on DuckDB 2.0.0 returns exactly that one row.

The delete filter belongs after ranking. Removing deletes before selecting the winner would resurrect entity 8's older value. This is one of the most important regression tests for any current-state model built from history.

Prove that the order is total

Check null entity identifiers, missing timestamps and duplicate (entity_id, changed_at, source_seq) tuples before running the selection. If two rows share every ordering key but disagree on content, the input violates the contract. Arbitrarily choosing one hides a producer defect.

If duplicate messages are legitimate, define an event identifier and deduplicate exact repeats first. If conflicting events reuse the same identifier, quarantine them. A stable answer is not automatically a correct answer: sorting by a random UUID may remove nondeterminism while inventing a business precedence rule.

Decide whether event time or source order is authoritative. A source sequence may represent the true update order even when timestamps arrive out of order. In that system, sequence should be the leading ordering key. Document the choice and test a late-arriving event that distinguishes the alternatives.

Keep history and current state separate

A current-state result is convenient for joins and dashboards. It is not a replacement for event history when audit, replay or correction matters. Retain the source position and event identifier that produced each selected value where appropriate.

Test the query after shuffling insertion order. The selected result should remain unchanged. Add explicit tests for ties, deletes, duplicate deliveries and late events, then test downstream joins against the selected-key uniqueness rule.

The durable lesson is simple: deterministic SQL follows a deterministic contract. Window functions implement the order; they cannot decide what “latest” means for your system.

Rank the complete event history before removing deleted entities. Filtering deletes first would resurrect an older row.