SQL / SORTING, LIMITING, AND BRANCHING OUTPUT
Sorting results with ORDER BY
Give any query a defined row order with ORDER BY, pick ASC or DESC, sort by unselected columns, and predict where NULL rows land.
What you will learn
- Add ORDER BY to get a defined row order; without it no order is guaranteed
- Use DESC to reverse a sort key; ASC is the default and can be omitted
- Predict where NULLs land and force it with NULLS FIRST or NULLS LAST
- Sort by a column that is not in the SELECT list
Understanding Sorting results with ORDER BY
A table is a set of rows, and a set has no first element. When you run a SELECT without ORDER BY, the sequence you get back is a side effect of how the engine happened to read the data: a full scan, an index it chose on its own, rows still sitting in cache, or several parallel workers finishing in whatever order they finish. That order can change when the table grows, when someone adds an index, or when a row is updated in place, and no engine treats such a change as a bug. ORDER BY is the only clause that turns row order into something the query actually promises.
ORDER BY acts on the rows the rest of the query already produced; it never adds or removes a row, it only decides the sequence they are handed back. That is why it is written at the end of the statement, after FROM and WHERE, and why sorting can never rescue a query that filtered the wrong rows. The sort key carries a direction: ASC is the default and is usually left out, DESC reverses it. The comparison itself comes from the column's type, so numbers compare numerically, dates chronologically, and text by the column's collation, which means what "ascending" means is decided by the type rather than by ORDER BY.
NULL means "no value", so it has no natural place in an ordering, and the SQL standard only requires that an engine put all NULLs consistently either before or after every real value. PostgreSQL and Oracle treat NULL as larger than anything, so it lands last under ASC and first under DESC; SQLite, MySQL and SQL Server treat it as smaller and do the opposite. When the sort column is nullable and the top or bottom of the output is what people read, write NULLS FIRST or NULLS LAST instead of inheriting whatever the local default happens to be.
CREATE TABLE trail (
name TEXT,
km REAL
);
INSERT INTO trail (name, km) VALUES
('Ridge Loop', 8.4),
('Creek Path', 3.1),
('Summit Spur', 12.7),
('Meadow Walk', 1.6);
SELECT name, km
FROM trail
ORDER BY km DESC;A result set has no row order of its own; ORDER BY is what creates one, comparing values by the sort column's type rules and by a fixed engine-specific position for NULL.
Worked examples
Where NULL lands
Shows the engine's default position for NULL in a sort and how to override it.
CREATE TABLE reading (sensor TEXT, celsius REAL);
INSERT INTO reading VALUES
('north', 21.5),
('south', NULL),
('east', 18.0);
SELECT sensor FROM reading ORDER BY celsius;
SELECT sensor FROM reading ORDER BY celsius NULLS LAST;Example explained
Line 1The first query says nothing about NULL, so SQLite applies its default of treating NULL as smaller than any number, putting 'south' at the top.
Line 2NULLS LAST overrides that default, so 18.0 and 21.5 come first in ascending order and the row with no reading falls to the end.
Line 3PostgreSQL and Oracle default the other way, so the first query's output is reversed at the NULL row there while the second query is identical everywhere.
Line 4MySQL 8 rejects the NULLS LAST syntax outright, which is one reason to check the position rather than assume it.
A number stored as text
Demonstrates that the sort follows the column's type, not what the values look like.
CREATE TABLE part (code TEXT, qty TEXT);
INSERT INTO part VALUES
('A1', '9'),
('A2', '10'),
('A3', '100'),
('A4', '2');
SELECT code, qty FROM part ORDER BY qty;Example explained
Line 1qty is declared TEXT, so ORDER BY compares the values character by character instead of by magnitude.
Line 2'10' sorts before '2' because the first characters '1' and '2' settle the comparison and the length of the string never enters into it.
Line 3'10' sorts before '100' because when one string is a prefix of the other, the shorter one counts as smaller.
Line 4The clause is not the bug here; the column type is, and declaring qty as INTEGER makes the same ORDER BY produce 2, 9, 10, 100.
Sorting by a column you did not select
Shows that the sort key does not have to appear in the output.
CREATE TABLE city (name TEXT, population INTEGER);
INSERT INTO city VALUES
('Lisbon', 545000),
('Porto', 231000),
('Braga', 193000);
SELECT name FROM city ORDER BY population DESC;Example explained
Line 1population is absent from the SELECT list, yet ORDER BY can use it because the rows being sorted still carry every column that FROM produced.
Line 2DESC on population puts Lisbon first, so the output is ordered by something the reader cannot see in the result.
Line 3This freedom disappears once the query collapses or combines rows, such as with SELECT DISTINCT or a set operator, where the sort key must be part of the result.
Important notes
ORDER BY inside a subquery, view, or CTE is not binding: the outer query may re-sort those rows or return them in any order, so the ORDER BY that counts belongs on the outermost SELECT.
Sorting is not free. Without an index that already supplies the order, the engine must buffer the whole result and sort it, which is why ORDER BY on a large table can dominate the runtime.
Common mistakes
Trusting the order of a SELECT that has no ORDER BY because it looked sorted during testing; the day an index or a larger table changes the plan, the report silently reorders and nobody changed the SQL.
Placing ORDER BY before WHERE, which is a syntax error rather than a slow query, because ORDER BY must follow the FROM and WHERE clauses.
Assuming rows that tie on the sort key keep a fixed relative order; two runs of the same query can interleave equal values differently, which looks like random shuffling inside each group.
Try it yourself
Change, predict, then run
In a browser SQL editor, create a table of five songs with a title and a duration in seconds, then write one query that returns only the titles with the longest song first. Set one duration to NULL, re-run it, and note whether your engine puts that row at the top or the bottom.
Open the SQL workspaceCheck your understanding
A reporting query has no ORDER BY and has returned rows in ascending id order for months. After a colleague adds an index on an unrelated column, the same query starts returning rows in a different order. What is the best explanation?
- Nothing ever guaranteed the old order; the engine now reaches the rows by a different access path, and only ORDER BY makes order part of the result
- Creating the index rewrote the table into the new index's order, so the rows are now physically stored that way
- The index damaged the primary key, and the order will stay wrong until the key is rebuilt
- SELECT returns rows in insertion order by default, and building the index re-inserted every row
Show answer
Without ORDER BY, the sequence is a by-product of whichever access path the planner picked, and a new index gives it a new option, so the old ascending-id order was a coincidence rather than a promise. The answer about the index rewriting the table is tempting because storage layout genuinely can change in some engines, but even there the query result carries no ordering guarantee; the fix is ORDER BY id, not reasoning about how rows sit on disk.