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.
Overview
Quick Context
A table is a set of rows, and a set has no order. Without an ORDER BY the database is free to return rows in whatever order came out of its plan — insertion order, index order, or whatever the parallel workers finished in. It often looks stable for months and then changes the day the table grows an index or the optimiser picks a different plan.
This is the single most common source of "it worked yesterday" bugs in reporting code. If the order matters, say so.
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.
ORDER BY: The Only Thing That Guarantees an Order
Without it a result set has no order at all — whatever you saw last time was luck.
The shape of the clause
ORDER BY expr [ASC | DESC] [NULLS FIRST | NULLS LAST], expr2 ...
Keys are applied left to right. The second key is consulted only for rows the first key could not separate, the third only for rows the first two could not, and so on. ASC is the default and almost never written out.
You can sort by things that are not columns: an expression such as salary * 12, an alias defined in the SELECT list, or a positional number such as ORDER BY2 meaning the second selected column. The positional form is compact and a menace — it silently changes meaning when someone edits the SELECT list.
Ties, and why a second key is not optional
Sorting by a column with duplicate values leaves the tied rows in an undefined order relative to each other. Combined with LIMIT, this produces one of the most confusing bugs in SQL:
SELECT * FROM orders ORDER BY order_date DESC LIMIT 10;
If twenty orders share the most recent date, which ten you get is arbitrary — and can change between runs. Page 2 of the results may repeat a row from page 1, or skip one entirely, because the two queries broke the ties differently.
The fix is a tiebreaker: add a unique column as the final sort key.
SELECT * FROM orders ORDER BY order_date DESC, id DESC LIMIT 10;
Now the order is total — no two rows compare equal — and pagination is stable. Make this a habit for any sorted, paginated query.
Where NULLs go
NULL is not a value, so "is it bigger or smaller?" has no natural answer, and engines disagree. PostgreSQL and Oracle treat NULL as the largest value: NULLS LAST for ASC, NULLS FIRST for DESC. MySQL and SQLite treat it as the smallest, so they do the opposite. SQL Server has no NULLS clause at all and sorts NULLs first.
If it matters, be explicit. Where the syntax is unavailable, sort by a flag first: ORDER BY (bonusIS NULL), bonusDESC.
When it runs, and what it costs
ORDER BY is almost last in the logical order of a query: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. That is why an alias from the SELECT list is usable here but not in WHERE, and why LIMIT takes the top of the sorted result rather than an arbitrary handful of rows.
Sorting is O(n log n) and needs memory; when the set does not fit, the engine spills to disk and the query gets dramatically slower. An index whose column order matches the ORDER BY can remove the sort entirely, because the index is already in that order — which is also why (a ASC, b DESC) may not be servable by an index on (a, b).
Sorting is not free, and not automatic
Without ORDER BY, a database makes no promise at all about row order. It may return rows in insertion order, in index order, in whatever order the storage engine found them, or in a different order every time depending on parallelism and caching.
This surprises people because small test tables usually come back in a stable-looking order. Then the table grows, the plan changes, and a report that "always" listed the newest first quietly starts listing something else. The rule is simple: if the order matters, say so.
SELECT name, salary, hired_on
FROM employees
ORDER BY salary DESC, name ASC;
ASC is the default and can be omitted; DESC must be written per column, so ORDER BY a, b DESC sorts a ascending and only b descending.
Where NULLs land, and how to control it
ORDER BY has to put missing values somewhere, and engines disagree on where.
Engine
Ascending
Descending
PostgreSQL, Oracle
NULLs last
NULLs first
MySQL, SQLite
NULLs first
NULLs last
SQL Server
NULLs first
NULLs last
PostgreSQL and Oracle let you say it explicitly with NULLS FIRST / NULLS LAST. Elsewhere, a portable trick works everywhere:
ORDER BY (col IS NULL), col; -- false (0) sorts before true (1): NULLs last
The same technique generalises to custom orderings. To sort statuses in a business order rather than alphabetically, sort by a CASE:
ORDER BY CASE status
WHEN 'urgent' THEN 1
WHEN 'open' THEN 2
WHEN 'closed' THEN 3
ELSE 4
END, created_at DESC;
What sorting costs, and how an index removes it
A sort is one of the more expensive things a query can do. Cost grows faster than linearly with the number of rows, and if the data does not fit in the sort memory the engine spills to disk, at which point the query gets dramatically slower.
Two ways to avoid paying:
Sort fewer rows. Filter first. ORDER BY runs after WHERE, so a selective filter shrinks the input to the sort.
Let an index provide the order. An index on (salary DESC) is already sorted, so the engine can read it in order and skip the sort entirely. In EXPLAIN output you will see the sort node disappear. For a composite index to help, the ORDER BY must match its columns from the left, in the same direction or exactly reversed.
This combination — an index on (status, created_at DESC) serving WHERE status = 'open' ORDER BY created_at DESC LIMIT 20 — is what makes a busy list view fast. The engine seeks to the first matching entry and reads twenty rows, touching almost nothing.
Sorting, ties, and where NULL goes
ORDER BY is the one clause whose absence has no defined behaviour -- without it a database may return rows in any order it likes. This shows that, along with the two details that decide real orderings: how ties break and where NULLs land.
query.sqlSQLite
-- Sort by something with ties, and watch what happens to the tied rows.
SELECT c.name,
c.city,
count(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name, c.city
ORDER BY orders DESC;
-- Dara and Eze both have 0 orders, so their relative order is not
-- determined by that ORDER BY. SQLite happens to return one of them
-- first; nothing promises it will tomorrow, or on another engine, or
-- once the table grows and the plan changes.
--
-- Add ", c.name" to the ORDER BY and the tie is broken deterministically.
-- That is the fix for every "the rows come back in a different order
-- sometimes" bug: give the sort enough columns to be total.
SELECT name, city FROM customers ORDER BY city;
-- And note where Dara lands. SQLite sorts NULL first ascending; other
-- engines put it last, and PostgreSQL lets you say NULLS FIRST or
-- NULLS LAST. If it matters, write COALESCE(city, 'zzz') or an explicit
-- CASE rather than relying on the default.
Result
Try it yourself
Sort by salary. Rows slide into place and eight of them light up amber — those are the ties. "Order is decided" reads NO, because your query has not said what should happen inside a tie.
Flip the direction. Set Direction to DESC and the whole list reverses. Note the tied pairs: their internal order does not simply mirror, because reversing the comparison does not reverse the rows the comparison never separated.
Break the ties. Set Then By to name. The amber highlighting clears, "Order is decided" flips to YES, and the result is now reproducible.
Sort by something unique. Set Order By to name. One key is enough here, because no two employees share a name.
Meet the NULLs. Set Order By to bonus. Three rows have no bonus; with NULLS LAST they sink to the bottom.
Move them. Switch NULLS Placement to NULLS FIRST and those three jump to the top without any other row changing place. This is the setting MySQL and PostgreSQL disagree about by default.
In one line
A result set has no order unless ORDER BY gives it one, and the order is only deterministic once your keys separate every pair of rows — which is why a unique tie-breaker belongs on anything paginated. Keys apply left to right, each one consulted only for the ties the previous keys left behind. NULL placement is engine-specific, so write NULLS FIRST or NULLS LAST rather than trusting a default. And because the sort runs near the end of the query, it can see SELECT aliases, it feeds LIMIT, and it costs real time and memory unless an index already holds the rows in the order you asked for.
Pagination that stays correct
The familiar approach is LIMIT and OFFSET:
SELECT * FROM orders ORDER BY id DESC LIMIT 20 OFFSET 40; -- page 3
It has two problems at scale. The database must produce and discard every skipped row, so OFFSET 100000 reads a hundred thousand rows to return twenty. And if rows are inserted or deleted between requests, the window shifts — a user paging forward can see the same row twice or miss one.
Keyset pagination (also called seek pagination) fixes both by remembering where the last page ended:
SELECT * FROM orders
WHERE (order_date, id) < ('2026-08-01', 5312) -- the last row of page 2
ORDER BY order_date DESC, id DESC
LIMIT 20;
Every page costs the same, because the index seeks straight to the position. The trade-off is that you can only move forward and backward, not jump to page 47 — which for infinite-scroll interfaces is not a trade-off at all.
Questions people ask
Can I sort by a column not in SELECT? Yes in most cases — ORDER BY runs after SELECT but can still reference the underlying table's columns. The exception is with SELECT DISTINCT or UNION, where the sort applies to the result set and the column must be present.
Can I use a SELECT alias in ORDER BY? Yes. ORDER BY is evaluated after SELECT, so aliases are available — unlike in WHERE.
Is ORDER BY 1 valid? Yes, sorting by the first selected column, and it is fine in ad-hoc queries. In saved code it is fragile: reorder the SELECT list and the sort silently changes.
How do I sort text case-insensitively? Depends on the collation. ORDER BY LOWER(name) works everywhere but prevents index use; a case-insensitive collation on the column is the better fix.
Does ORDER BY in a subquery or view carry through? Not reliably. The outer query is free to reorder, and several engines ignore an inner ORDER BY entirely unless it is paired with LIMIT. Put the ordering on the outermost query.
Why is my sort slow only sometimes? Almost always because it fits in memory for small inputs and spills to disk past a threshold. Raising the sort memory setting or adding a matching index are the two fixes.
Recap in one screen
Without ORDER BY there is no guaranteed order, whatever small tests suggest.
Always add a unique tiebreaker when sorting a column with duplicates, especially with LIMIT.
NULL placement differs by engine — state it explicitly, or use ORDER BY (col IS NULL), col.
Sorting is expensive; filter first, and let a matching index remove the sort altogether.
For deep pages, keyset pagination beats OFFSET on both speed and correctness.
Predict, then reveal
About to run: Sort by salary. 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.
Without scrolling back — what is the one-line takeaway from this module?
A result set has no order unless ORDER BY gives it one, and the order is only deterministic once your keys separate every pair of rows — which is why a unique tie-breaker belongs on anything paginated. Keys apply left to right, each one consulted only for the ties the previous keys left behind. NULL placement is engine-specific, so write NULLS FIRST or NULLS LAST rather than trusting a default.
What does this module say about “Quick Context”?
A table is a set of rows, and a set has no order. Without an ORDER BY the database is free to return rows in whatever order came out of its plan — insertion order, index order, or whatever the parallel workers finished in. It often looks stable for months and then changes the day the table grows an index or the optimiser picks a different plan.
What does this module say about “Ties, and why a second key is not optional”?
Sorting by a column with duplicate values leaves the tied rows in an undefined order relative to each other. Combined with LIMIT , this produces one of the most confusing bugs in SQL:
Cheat sheet
ORDER BY in SQL
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.
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.