Deterministic Ordering

Deterministic ordering means sorting rows by columns that leave no ties, so that the same data always comes back in the same order. It matters because SQL promises no order at all unless one is requested, and even a requested order says nothing about rows that are equal on every sort column.

What SQL does and does not promise

PostgreSQL’s documentation is explicit on both points. Without ORDER BY, rows come back in an unspecified order that depends on the scan and join plan and on storage layout, and must not be relied on. With ORDER BY, later sort expressions order only the rows that earlier ones leave equal — so rows equal on all of them are still in no guaranteed order. The actual order often looks stable in testing, which is exactly why reliance on it goes unnoticed until data grows, a plan changes, or the query moves to another engine.

Where ties change results, not just display

• LIMIT and top N. The documentation for LIMIT warns that without an order that is unique, a query returns an unpredictable subset of rows. “Top 10 customers” with two customers tied for tenth place can return either.
• Pagination. Because different LIMIT and OFFSET values can lead to different plans, the documentation notes that fetching pages this way gives inconsistent results unless the order is predictable. A row can appear on two pages, or on none. A unique order removes that cause and leaves another: pages read at different times see different data. Insert a row that sorts before page one after page one was read, and the next OFFSET page repeats a row; delete one and it skips. A keyset cursor over keys that do not change (WHERE id > :last_id ORDER BY id) stops that shifting, but it does not freeze the data — a value changed between page reads is old on the pages already read and new on the rest, so the combined result may match no state the table was ever in. Stable traversal and a single-moment export are different guarantees; the second needs every page read from the same snapshot, or a single statement under an engine that reads consistently.
• Deduplication with ROW_NUMBER. Keeping “the latest version per key” with ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC) picks arbitrarily between versions that share a timestamp; PostgreSQL’s window tutorial states tied rows are numbered in an unspecified order.
• Order-dependent aggregates. Functions such as string_agg or array_agg produce results that depend on input order, which is unspecified unless an ordering is given inside the aggregate.

Ranking functions are the exception: RANK and DENSE_RANK give every tied row the same value by definition, so they are stable, but they can return more than N rows for a “top N” filter. That is often the more honest answer.

Choosing a tie-breaker

The usual fix is to append a column to the sort that makes it unique: ORDER BY revenue DESC, customer_id. A good tie-breaker has three properties.

• Unique, together with the columns before it — otherwise ties remain.
• Stable across reloads — a surrogate generated fresh on each load changes the winner every run.
• Meaningful where the choice matters. For display, any unique key will do. For deduplication, the tie-breaker must reflect the order the changes happened at the source — a version the source assigns to each change of that entity, or a log position, and then only among positions from the same log, since positions from different partitions or different databases are not comparable. An ingestion sequence qualifies only where arrival order is guaranteed to preserve source order, or where the business rule genuinely is “whatever arrived last”: a delayed “paid” loaded after “refunded” gets the higher ingestion number and flips the status back. Sorting “paid” and “refunded” alphabetically is deterministic and wrong.

If no column in the data can decide which of two rows should win, making the choice deterministic only hides the problem consistently. Counting ties is a cheap check to run before trusting any order-dependent step:

-- Keys where the latest timestamp is shared by more than one row,
-- including keys whose timestamps are all NULL (no latest at all)
SELECT order_id, updated_at, COUNT(*) AS rows_at_latest
FROM (
    SELECT order_id, updated_at,
           MAX(updated_at) OVER (PARTITION BY order_id) AS latest
    FROM status_log
) t
WHERE updated_at = latest
   OR (updated_at IS NULL AND latest IS NULL)
GROUP BY order_id, updated_at
HAVING COUNT(*) > 1;

The second condition is there because MAX ignores NULL: a key whose timestamps are all NULL has no latest, and without it the check returns nothing for that key and reads as “no ties” when the truth is “no way to tell.” Rows with a NULL timestamp beside dated ones are treated here as never the latest, which matches sorting NULLs last; a different NULL policy needs the check changed to match. A non-empty result is a data question to raise with the owner of the source, not something to settle with an arbitrary sort. One more portability note: the default position of NULLs in a sort also differs between engines — PostgreSQL places them last in ascending order and MySQL first — so a sort on a nullable column should state NULLS FIRST or NULLS LAST where the engine supports it.

Ordering sits alongside joins, NULLs, and aggregation levels among the ways valid SQL returns wrong answers, worked through in When Correct SQL Returns the Wrong Answer.

Sorting what arrived is not controlling what arrives

One boundary is worth stating, because the word “ordering” spans two problems that need different answers. Everything above is about a query over rows you already hold: given this data, return it in the same order every time. That is a property of the sort you write.

It says nothing about the order in which records reach you. A message published earlier can be delivered later — a parallel publisher, several broker partitions, a retry after a subsequent success, a record replayed from a dead letter queue. No ORDER BY reaches those, for the simple reason that a sort cannot include a row that has not arrived: a query ordered perfectly at 10:00 gives a different answer at 10:05 when the late record lands.

The delivery contract and the consumer’s purpose determine what may be skipped. For a latest-state projection, a complete state and an atomic source-version guard can discard superseded states. Scope versions to the same entity and source generation, handle equal-version conflicts, and retain versioned deletion evidence against stale resurrection. This does not recover every intermediate event or external action. A final refunded state does not prove that every obligation attached to paid was performed.

For partial changes, starting at {status: open, amount: 100}, applying v2 = {amount: 80} and dropping v1 = {status: paid} loses the status change. Use source ordering or a merge rule that preserves each required effect. Order-dependent commands need predecessor handling and recovery of missing work. Unconstrained integer increments with no order-sensitive effects can instead apply in any order, provided duplicate recognition is atomic with each effect. A single partition preserves the relay’s publication order, not an earlier order it already lost. Retained events or business records recover missing obligations; a current snapshot can repair a projection but not missing history. The source-based tie-breaker used in a query reconstructs the ordering of records already present—it cannot supply records that never arrived.

Further reading

• PostgreSQL Documentation, Sorting Rows (ORDER BY)
• PostgreSQL Documentation, LIMIT and OFFSET
• PostgreSQL Documentation, Window Functions
• MySQL 8.4 Reference Manual, Problems with NULL Values