📶ORDER BY
The only portable way to pin row order
Without ORDER BY, relational engines return rows in whatever physical order is cheapest today, clustered index, hash probe order, parallel merge-not insertion order. Tests that “always passed” can flap when statistics change. Production APIs must treat sort as part of the contract.
🪜Multiple keys = lexicographic sort
First column primary order; ties broken by second; still tie? third, until you run out or add an id.
Use ASC (default) or DESC per key. For lab inventory or freezer maps, secondary sorts often encode physical layout (shelf, then box label).
Primary + secondary sort
SELECT freezer_id, shelf, box_label
FROM storage_map
WHERE site = 'BOS1'
ORDER BY freezer_id ASC, shelf DESC, box_label ASC;| freezer_id | shelf | box_label |
|---|---|---|
| F-01 | 4 | BX-001 |
| F-01 | 3 | BX-018 |
| F-02 | 2 | BX-002 |
Sort by SELECT list position (portable but brittle)
SELECT primer_id, tm_celsius
FROM oligos
ORDER BY 2 DESC; -- 2nd column in SELECT list| primer_id | tm_celsius |
|---|---|
| O-991 | 72.4 |
| O-774 | 68.1 |
| O-120 | 61 |
ORDER BY mrn without tie-breaker
Ties reorder when data changes; pagination cursors jump rows between requests even though “nothing changed” from the user’s perspective.
Add deterministic key (sample_id, pk, …)
Always append a unique key: ORDER BY mrn, patient_id.
Some engines support NULLS FIRST / NULLS LAST to control where NULLs appear, critical for reproducible pagination when nullable columns participate in the sort.
Keyset pagination reminder
For infinite-scroll UIs, prefer WHERE (sort_col, id) > ($last_sort, $last_id) over deep OFFSET: tail latency stays flat as users scroll.
TL;DR
ORDER BY sorts the final result set (logically after SELECT); tie-break with a unique key so ordering is stable. ASC/DESC and NULLS FIRST/LAST (where supported) change semantics for nullable sorts. Sorting large outputs without a supporting index can mean expensive memory sorts, add indexes aligned to common ORDER BY patterns or use keyset pagination for deep pages.