Stable Sorting

Definition

A sort is called stable if rows with equal sort-key values always come back in the same, repeatable relative order, run after run. Plain SQL ORDER BY on a column that is not unique does not guarantee this by default — when two or more rows tie on every column listed in ORDER BY, the relative order of those tied rows is left to the engine, and it is legal (and observed in practice) for it to differ between executions, especially after the table is updated, reorganized (e.g. VACUUM, OPTIMIZE TABLE), or the query planner picks a different execution path.

Worked Example: The Problem

sql
SELECT name, category FROM products ORDER BY category;

If three products share category = 'Electronics' (Wireless Mouse, Bluetooth Speaker, Phone Case), SQL guarantees they will all appear together and before/after other categories correctly — but it does not guarantee whether Wireless Mouse comes before or after Phone Case within that Electronics group. Run the same query again after an unrelated UPDATE or VACUUM, and their relative order could change, even though nothing about category itself changed. This is a real, observed source of "flaky" tests and inconsistent UI ordering (e.g. a paginated list where the same row appears twice across two page loads, or never appears, because the tie-order shifted between the two queries).

The Fix: Add a Unique Tiebreaker

Append a column (or combination of columns) known to be unique — the primary key is the natural choice — as the final ORDER BY key:

sql
SELECT name, category FROM products
ORDER BY category, id;   -- id is UNIQUE, so ties are now fully broken

Now every row has a completely distinct (category, id) pair, so there are no remaining ties at all — the sort becomes fully deterministic: the same query against the same data will produce the exact same row order, every single time, regardless of engine internals, plan changes, or physical storage reshuffles.

Why This Matters for Pagination Specifically

Stable, deterministic ordering is essential for correct pagination (both LIMIT/OFFSET and Keyset). If page 1 and page 2 are fetched with two separate queries and the sort has unresolved ties, a tied row could appear on both pages (duplicated) or on neither page (skipped) if its relative position shifts between the two queries. Ending ORDER BY in a unique column eliminates this risk entirely — a prerequisite for trustworthy pagination.

sql
-- Safe for pagination: fully deterministic order
SELECT id, name FROM products ORDER BY rating DESC, id ASC LIMIT 5 OFFSET 5;

Key Takeaways

• Q: Does plain ORDER BY on a non-unique column guarantee the same tie order every run?

A: No — ties are unresolved and their order is not guaranteed across executions.

• Q: What is the fix to make a sort deterministic?

A: Add a final, guaranteed-unique column (typically the primary key) to the end of ORDER BY.

• Q: Why does this matter beyond aesthetics?

A: Non-deterministic tie order in pagination can cause rows to be duplicated across pages or silently skipped entirely.