SQL / SORTING, LIMITING, AND BRANCHING OUTPUT
Sorting on expressions and multiple columns
Order rows by several keys with independent ASC/DESC directions and sort on computed expressions such as totals, ratios, or cast values.
What you will learn
- ASC and DESC bind to one key at a time, never to the whole ORDER BY list
- Use any per-row expression as a sort key, including one not in the SELECT list
- Cast numeric text and force float division so the key compares the way you expect
- Finish the key list with a unique column so tied rows come back in a fixed order
Understanding Sorting on expressions and multiple columns
A list of sort keys is compared the way words are compared in a dictionary: the engine looks at the first key, and only when two rows are equal there does it look at the second. That is why ORDER BY region, total DESC never lets a huge total jump across a region boundary; total is not consulted at all for two rows whose regions differ. Each key also carries its own direction, so ASC and DESC are modifiers on a single key rather than on the clause, and ORDER BY a, b DESC sorts a ascending and only b descending.
A sort key does not have to be a stored column. ORDER BY is applied to rows that already exist, so anything that produces one value per row can be a key: arithmetic across two columns, LENGTH(title), CAST(size AS INTEGER), a function call, a CASE. The engine evaluates that expression once per row and sorts the resulting values, which is the part worth internalising: the comparison follows the type of the expression's result, not the appearance of the source column. As text '10' sorts before '9'; as integers 10 sorts after 9, so adding a CAST to the key changes the order even though the selected data is identical.
Because ties fall through to the next key, the last key decides how much of the ordering is actually pinned down. Rows that compare equal on every listed key may be returned in any order, and that order can shift when rows are inserted or when the engine picks a different plan, since nothing in SQL promises to preserve a previous arrangement. Ending the key list with a unique column, usually the primary key, costs one extra comparison per tie and makes the result reproducible.
CREATE TABLE orders (
id INTEGER,
region TEXT,
units INTEGER,
unit_price REAL
);
INSERT INTO orders (id, region, units, unit_price) VALUES
(1, 'east', 3, 20.0),
(2, 'west', 5, 4.0),
(3, 'east', 2, 35.0),
(4, 'west', 1, 25.0),
(5, 'east', 6, 7.5);
SELECT id, region, units * unit_price AS total
FROM orders
ORDER BY region ASC, units * unit_price DESC;A multi-column ORDER BY is a left-to-right tie-break chain, and every position in that chain can hold any per-row expression with its own direction.
Worked examples
The second key only reorders ties
Shows that a high value in a later key cannot lift a row past a row with a better earlier key.
WITH scores(player, tier, points) AS (
VALUES ('ana', 2, 900),
('bo', 3, 700),
('cy', 2, 900),
('di', 3, 700),
('ed', 2, 400)
)
SELECT player, tier, points
FROM scores
ORDER BY tier DESC, points DESC, player ASC;Example explained
Line 1tier DESC is the primary key, so every tier 3 row precedes every tier 2 row whatever the points are.
Line 2ana's 900 lands below di's 700 because points is read only after the tiers compare equal.
Line 3player ASC settles the two remaining ties (bo/di and ana/cy) so the result is fixed rather than engine-dependent.
Casting a numeric text column in the key
Sorts digits stored as text in numeric order by converting the value inside ORDER BY.
WITH files(name, size_text) AS (
VALUES ('a.log', '9'),
('b.log', '10'),
('c.log', '100'),
('d.log', '2')
)
SELECT name, size_text
FROM files
ORDER BY CAST(size_text AS INTEGER);Example explained
Line 1ORDER BY size_text alone would return 10, 100, 2, 9, because text comparison works character by character.
Line 2CAST(size_text AS INTEGER) produces a numeric key per row, so 9 correctly sorts below 10.
Line 3The selected column is untouched: the cast changes the key, not the output values.
A ratio key that is never selected
Ranks rows by a computed win rate that does not appear in the SELECT list.
WITH teams(team, wins, losses) AS (
VALUES ('ants', 9, 3),
('bats', 4, 4),
('cats', 7, 7),
('dogs', 1, 1)
)
SELECT team, wins, losses
FROM teams
ORDER BY wins * 1.0 / (wins + losses) DESC, wins DESC;Example explained
Line 1The key wins * 1.0 / (wins + losses) is legal even though it is absent from the SELECT list, because sorting happens over rows that are already assembled.
Line 2The * 1.0 forces floating-point division; wins / (wins + losses) on two integers truncates to 0 for every row and destroys the ranking.
Line 3Three teams tie at 0.5, so wins DESC decides the block and places cats above bats above dogs.
Important notes
An expression that returns NULL for some rows clumps them at one end of the result, and the end depends on the engine: SQLite and MySQL place NULLs first in an ascending sort, PostgreSQL and Oracle place them last. Add NULLS FIRST or NULLS LAST where it is supported if the placement matters.
A plain index on price cannot satisfy ORDER BY price * 0.9 or ORDER BY LOWER(name); the engine materialises the rows and sorts them unless an index exists on that exact expression, which starts to matter on large tables.
Common mistakes
Writing ORDER BY region, total DESC when both were meant to descend: DESC attaches to total only, regions still come back east before west, and it looks as though the engine ignored the DESC.
Adding a second key hoping it will pull a row upward. With ORDER BY tier DESC, points DESC a 900-point tier 2 row still sits under every 700-point tier 3 row; the fix is to reorder the keys, not to add more of them.
Sorting on a key whose type was never checked: a text column of digits puts 100 before 2, and integer division in a ratio key collapses to 0 for every row, so the output looks random while ORDER BY is doing exactly what it was told.
Try it yourself
Change, predict, then run
Create a five-row table products(name TEXT, price REAL, grams INTEGER) and sort it so the lowest price per gram comes first with name as the tie-breaker. Then move grams DESC in front of the ratio key and identify which rows changed position and why.
Open the SQL workspaceCheck your understanding
A query ends with ORDER BY region ASC, total DESC. Row X is region 'west' with total 900; row Y is region 'east' with total 10. Which statement is true?
- Y comes first, because region is compared first and 'east' sorts before 'west'
- X comes first, because a DESC key outranks an ASC key when the two disagree
- X comes first, because the largest total always ends up in the first row
- The order is unspecified, because the two keys sort in opposite directions
Show answer
The regions differ, so the comparison is settled by the first key and total is never examined for this pair, putting Y first. The tempting answer is that the largest total leads: total does rank rows, but only inside a single region, and DESC is a per-key modifier that gives that key no priority over the keys to its left.