Change the sort key and the rows physically move. Add a direction, a tie-breaker and a NULLS placement, and watch the query text build itself as you go.
10 rows
#namedeptsalaryhiredbonus
The # column is the row's position in the unsorted table, so you can see exactly which rows moved and which ones only kept their old order because nothing told them otherwise.
Overview
Tables represent rows, not a sorted list. An index or insertion history may make results appear ordered, but SQL does not guarantee that order without ORDER BY. Add it whenever sequence matters, including reports, exports, and pagination.
Ask for the order you need
query.sqlSQLite
SELECT order_id, created_at
FROM orders
ORDER BY created_at DESC, order_id DESC;
Result
Sorting by only created_at leaves ties unordered. A stable unique tie-breaker such as order_id makes the requested order deterministic.
NULLs and collations
The placement of NULL values and text comparison rules can vary by engine and collation. PostgreSQL supports NULLS FIRST and NULLS LAST; SQLite also supports explicit NULL ordering in current versions. Text order can depend on locale and collation. Test the target database when results must be portable.
Sorting has a cost
The engine may satisfy an order using an index or sort rows at runtime. An index matching the filter and order can help, but adds storage and write cost. Check the plan rather than assuming either that an index is used or that every sort is expensive.
A final ORDER BY controls the result order; ordering inside a subquery does not generally guarantee the outer query's order. Specify order at the query level whose output the user consumes. See LIMIT and OFFSET and indexes.
Make a ranking deterministic
If the question is “top ten orders by total,” use ORDER BY total DESC and add a unique ID as the next key. Otherwise, orders with equal totals can exchange positions between executions. If NULL totals are possible, decide whether they should appear first or last rather than relying on a dialect default. Also check whether total is computed or stored: ordering by an expression may need a matching expression index or a sort. The output is only a stable ranking when all ordering ties are resolved.
Limit only after a complete order
For a top-N query, apply LIMIT to the same SELECT whose ORDER BY expresses the ranking. If only the first key is ordered and ties remain, the chosen N rows can vary. If the ordering key can change between requests, even a tie-breaker may not preserve a user's place in the list; use a stable snapshot or cursor policy when that matters. Sorting in the application after fetching a limited subset cannot recover rows the database did not return.
About to run: switch Order By to bonus. Before it does — what happens to the readout?
Committing to an answer first is the point — the reveal runs the experiment on the visualisation above and reads the real value back, so nothing here is scripted.
Recall check
0 of 3
Say the answer out loud before you reveal it — recalling it is what makes it stick, and rereading it is not.
What does this module say about “Ask for the order you need”?
What does this module say about “NULLs and collations”?
What does this module say about “Sorting has a cost”?
Cheat sheet
ORDER BY in SQL
Watch rows slide into their new order as you change the sort key, direction, tie-breaker and NULLS placement, with the query building live.
Ashish Jangra builds and maintains VizLearn. Every module here is written and the visualisation behind it hand-built, so the numbers in a readout come from the same code that draws the picture. Corrections are genuinely welcome and get priority over everything else — if a page states something wrong, or an animation misrepresents what the algorithm does, get in touch.