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.
• 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
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
GROUP BY order_id, updated_at
HAVING COUNT(*) > 1;

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.

Documentation checked in September 2026 (PostgreSQL 18, MySQL 8.4). 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.

Which is why the two problems have different solutions, and why the tie-breaker rules above matter more than they first appear. Delivery order is handled at the producer and transport — carry a version the producing transaction assigns to the entity, and key messages so one partition holds that entity’s history. Whether a consumer may then skip a version it has already passed depends on what the message carries, and the distinction matters: if each message holds the entity’s complete state, skipping an older one is safe because the newer one subsumes it; if messages carry changes, skipping loses them. From {status: open, amount: 100}, a v1 of {status: paid} discarded after a v2 of {amount: 80} has arrived leaves an unpaid order at the new amount, with nothing logged as wrong. Change-carrying streams need ordering per entity — hold a message until its predecessor is applied, and have a way to recover one that never comes. Then the ordering you apply on read is a reconstruction from those source-assigned versions rather than an assumption about arrival, which is exactly the reason a deduplication tie-breaker must use a source version or log position and not an ingestion sequence.

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