ORDER BY

Syntax

ORDER BY column_name [ASC | DESC] [NULLS FIRST | NULLS LAST], ...

Why it matters

Without a unique tiebreaker, the order of rows that share the same value is not guaranteed. That can affect paging, ranking, and results that are later exported or compared.

Loading editor…

Where NULLs sort

NULL isn't a value to compare, so engines pick a side — and they don't all pick the same one. This is the single most common cross-engine ORDER BY surprise.

Loading editor…

cancelled_at is NULL for every order that hasn't been cancelled — in SQLite those NULL rows sort first. On Postgres the identical query would put them last. If a report or a "top N" query depends on which end the NULLs land, say so explicitly instead of relying on the default.

Forcing it explicitly

-- Postgres, Oracle, SQLite 3.30+
ORDER BY cancelled_at ASC NULLS LAST;

-- SQL Server / older engines: push NULL to a synthetic sort key
ORDER BY CASE WHEN cancelled_at IS NULL THEN 1 ELSE 0 END, cancelled_at ASC;

Good practice

• add a stable tie-breaker such as a primary key or timestamp
• state where NULLs should land instead of relying on the engine's default
• document the business rule behind the order
• be explicit when results are expected to be stable across runs

Safety check before shipping

If the query is used for pagination or ranking, verify that the ordering is deterministic enough to support repeated results and audits — and that a NULL in the sort column doesn't put a row somewhere unexpected.