ORDER BY compares its expressions in sequence, and an explicit NULL sort key makes missing-value placement predictable across engines.
A report sorted by amount may still change order when two amounts tie. Add a stable tie-breaker and decide where missing values belong. This matters for readable tables, deterministic tests and pagination boundaries.
This reference adds detail to the main course. You can read it independently: the small database below supplies every table and row used in the examples. Start with the question each query answers, then compare the returned values with the data rather than memorising a line of syntax.
Set up the practice database
Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.
Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(40) NOT NULL,
city VARCHAR(40),
manager_id INTEGER
);
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
amount DECIMAL(10,2),
status VARCHAR(20) NOT NULL,
created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
(1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
(3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
(101,1,10,'paid','2026-01-01'),
(102,1,30,'pending','2026-01-02'),
(103,2,20,'paid','2026-01-02'),
(104,3,NULL,'pending','2026-02-01'),
(105,3,20,'paid','2026-02-02'),
(106,NULL,5,'paid','2026-02-03');An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.
The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.
How Multiple-column and NULL sorting works
ORDER BY status ASC, amount DESC, id ASC first compares status. Only rows tied on status proceed to the amount comparison, and only rows tied on both use id. It does not separately sort the entire result by each expression. Each direction applies to its own expression, so ORDER BY status, amount DESC means ascending status and descending amount. Write directions explicitly when the combination could be overlooked.
NULL placement defaults vary. PostgreSQL normally puts NULL last for ascending order and first for descending, while MySQL, SQL Server and SQLite commonly use the opposite placement for their ordinary ascending and descending sorts. A portable explicit rule is ORDER BY CASE WHEN amount IS NULL THEN 1 ELSE 0 END, amount, id. The CASE produces zero for known amounts and one for missing amounts, placing unknown values last regardless of the engine’s native NULL ordering.
Unique tie-breakers and stable pages
A unique final tie-breaker creates a total order for result rows. Sales 103 and 105 both have twenty, so amount alone cannot specify their relative order. id makes the choice deterministic without changing the business measure. When pagination uses a composite order, the next-page condition must respect the same fields and directions. An output alias is generally usable in ORDER BY, but ordinal positions such as ORDER BY 2 are fragile because changing the SELECT list can silently change the sort.
Text order depends on collation, which can affect case, accents and language-specific ordering. Sorting a formatted numeric or date string can also differ from sorting its original typed value. Keep a typed sort key even if the display label is decorated. Sorting can require an expensive operation on large results; an index that matches the filter and ordering may help, but verify the execution plan. An index never removes the need to write ORDER BY when the output contract requires an order.
MySQL, PostgreSQL, SQL Server and SQLite
The CASE-based key works on all four systems. PostgreSQL and SQLite support explicit NULLS FIRST or NULLS LAST syntax. MySQL and SQL Server do not support that syntax as a shared ORDER BY feature. Collation names and available collations are also engine-specific, so specify an approved local collation when exact text ordering matters.
Check the engine-specific syntax and version requirements in PostgreSQL sorting, SQLite ordering.
Practice task and worked solution
Sort all sales by decreasing known amount with missing amounts last, and resolve equal amounts by increasing id.
Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.
SELECT id,amount FROM sales
ORDER BY CASE WHEN amount IS NULL THEN 1 ELSE 0 END,
amount DESC,id ASC;id | amount
102 | 30
103 | 20
105 | 20
101 | 10
106 | 5
104 | NULLThe missing-value key stays ascending so NULL remains last, while only the amount direction changes to descending. Both twenty-valued sales retain increasing id order. This demonstrates that each ORDER BY expression has its own direction and purpose.
Switch only amount ASC to amount DESC while leaving the missing-value CASE and id ascending. Predict the example’s reversed measure order with the two twenties still in id order and NULL still last. Remove the CASE and compare the local engine’s default. The purpose is to observe the default, not treat it as a portable rule.
SQL ORDER BY Multiple Columns and NULL Values Syntax
Multiple-column and NULL sorting
SELECT id,amount FROM sales
ORDER BY CASE WHEN amount IS NULL THEN 1 ELSE 0 END,
amount ASC,id ASC;SQL ORDER BY Multiple Columns and NULL Values Summary
ORDER BY a, b, ccomparesbonly whenaties, andconly when both tie.- Each sort item has its own direction, so
ORDER BY status ASC, amount DESCmixes directions. - End with a unique column, such as
id, so the order is completely determined and pages are stable. NULLplacement differs by database;NULLS FIRSTandNULLS LASTwork in PostgreSQL and SQLite.ORDER BY CASE WHEN amount IS NULL THEN 1 ELSE 0 END, amountputs missing values last everywhere.
SQL ORDER BY Multiple Columns and NULL Values Examples
Each example shows the code, the output it produces and what each part does.
Known amounts first, missing amount last
The first expression places known values before missing ones. The next expression arranges known amounts from small to large. The final id expression orders the two twenties consistently. Sale 104 comes last rather than relying on a dialect default.
SELECT id,amount FROM sales
ORDER BY CASE WHEN amount IS NULL THEN 1 ELSE 0 END,
amount ASC,id ASC;id | amount
106 | 5
101 | 10
103 | 20
105 | 20
102 | 30
104 | NULLCommon SQL ORDER BY Multiple Columns and NULL Values Mistakes
Relying on default NULL placement and ties
ORDER BY amount DESC does not resolve ties and places missing values according to the engine’s default. If a first-page result must be identical across runs and engines, add an explicit missing-value key and a unique tie-breaker. Do not assume selecting id makes id a sort key automatically.
SQL ORDER BY Multiple Columns and NULL Values in Real Projects
Ordering a work queue
An operations queue can show pending records first, larger amounts next and an id as the final stable key. Write an explicit status-priority CASE if alphabetical status order is not the policy. The order should describe the queue’s actual decision rule rather than depend on spelling coincidences.
Keep sort direction beside every field in the pagination contract. A descending amount and ascending id need a continuation predicate that follows both directions. Explicit missing-value placement prevents a move between database engines from shifting unknown records from the end to the beginning of a queue, even when the same known values remain correctly ordered.
SQL ORDER BY Multiple Columns and NULL Values FAQ
Does GROUP BY imply sorted output?
No. Some databases happen to return groups in order, but that's an implementation detail. Add ORDER BY whenever presentation or pagination depends on order.
Does DESC apply to every earlier column?
No. Each sort item has its own direction, and the default is ASC. In ORDER BY status, amount DESC, only amount is descending; write ORDER BY status DESC, amount DESC if both should be.
Can I order by a selected alias?
Usually yes for ordinary SELECT output, because sorting happens after the select list is computed. Special cases, such as SELECT DISTINCT and UNION queries, can impose additional restrictions on which expressions may appear in ORDER BY.
How do I sort by a custom order, such as pending before paid?
Sort by a CASE expression that assigns each value a rank: ORDER BY CASE status WHEN 'pending' THEN 1 WHEN 'paid' THEN 2 ELSE 3 END, id. Alphabetical order rarely matches a business priority, so make the rule explicit.
How do I sort text in natural number order, so item2 comes before item10?
Plain text sorting compares character by character, so item10 sorts before item2. Store the number in its own numeric column and sort by that, or sort by an extracted numeric part such as CAST(SUBSTR(code, 5) AS INTEGER); zero-padding codes, item02, also works.
SQL ORDER BY Multiple Columns and NULL Values Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
Which id appears last in the explicit NULL-last ascending sort?
SQL ORDER BY Multiple Columns and NULL Values Quiz
2 questions to check what you learned.
Why add id after amount in ORDER BY?
- To make every amount become unique by changing its saved value
- To resolve equal amounts with a deterministic tie-breaker
- To sort all ids before considering the amount
Which portable expression can put NULL amounts last?
- COALESCE(amount,0), which always preserves the original numeric order
- CASE WHEN amount IS NULL THEN 0 ELSE 1 END ascending, which puts NULL first
- CASE WHEN amount IS NULL THEN 1 ELSE 0 END as the first ascending sort key