Skip to main content
CodeSparring

Sorting with ORDER BY

Level 1: Level 1: SQL Foundations (Reading Source Data)easy11 minORDER BYASC/DESCmulti-key sortNULLS ordering behavior

Order output deterministically for previews and top-N inspection.

Rows have no inherent order

A table is a set. Without ORDER BY, the engine may return rows in any order, and that order can change between runs, after a reload, or when an index changes. For any preview, any "newest 10," any output a human or a test will eyeball, you must sort explicitly, and you must sort on enough columns to make the order deterministic.

Sort keys and tie-breaking

ORDER BY order_ts DESC puts newest first. But if two orders share a timestamp, their relative order is still undefined. Add a tie-breaker: ORDER BY order_ts DESC, order_id DESC. Now the output is stable every run.

Worked example:

SELECT order_id, order_ts, total_cents
FROM orders
ORDER BY order_ts DESC, total_cents DESC, order_id ASC;

Three rows share the newest-but-one timestamp, so total_cents DESC separates the 8000 row from the two 5000 rows. But those two 5000 rows tie on both keys, so order_id ASC is what finally pins them: without it, their relative order is whatever the engine happens to produce.

Anatomy:

ORDER BY  order_ts DESC ,   total_cents DESC ,   order_id ASC
          primary sort key  secondary key        unique tie-breaker
          DESC = high to low ; ASC (default) = low to high

Where NULLs land

In the warehouse this differs: SQLite sorts NULLs first under ASC (and last under DESC); Postgres defaults to NULLs last under ASC. If NULL placement matters, be explicit. Standard SQL supports ORDER BY col ASC NULLS LAST (Postgres/Oracle), though SQLite only added NULLS FIRST/LAST in 3.30. Portable trick: ORDER BY (col IS NULL), col forces NULLs last everywhere.

Pitfall. Sorting on a non-unique column alone is not deterministic. Always add a unique tie-breaker (often the primary key) if the output must be stable.

Recap. ORDER BY makes output deterministic; add a unique tie-breaker column, and be explicit about NULL placement across dialects.

Sample data for this example
CREATE TABLE orders (
  order_id    INTEGER,
  order_ts    TEXT,      -- ISO-8601, may repeat
  region      TEXT,
  total_cents INTEGER
);
INSERT INTO orders VALUES
  (1, '2026-03-02T10:00:00Z', 'EU', 5000),
  (2, '2026-03-02T10:00:00Z', 'US', 5000),
  (3, '2026-03-03T08:30:00Z', 'EU', 3000),
  (4, '2026-03-01T22:15:00Z', 'UK', 7000),
  (5, '2026-03-02T10:00:00Z', 'EU', 8000);
Worked example (SQL)
SELECT order_id, order_ts, total_cents
FROM orders
ORDER BY order_ts DESC, total_cents DESC, order_id ASC;

Apply

Your turn

The task this lesson builds to.

Sort orders by order_date descending, then by total_cents descending as a tie-breaker. Return order_id, order_date, total_cents. The output must come back in that exact sorted order.

3 hints and 1 automated check are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, so it survives past today.

Produce a deterministic newest-first preview whose order can never change between runs, even though several rows share a timestamp. Sort by order_ts descending, then total_cents descending, then order_id ascending as the final unique tie-breaker. Return order_id, order_ts, region, total_cents. The output must come back in that exact sorted order.

3 hints and 1 automated check are waiting in the workspace.