ORDER BY and Deterministic Sorting

Learning outcomes

A relational query result has no guaranteed presentation order unless an ORDER BY clause requests one. Physical storage order, index order, and yesterday’s observed output are not contracts.

ORDER BY creates the result sequence

SELECT    product_id,    product_name,    unit_priceFROM productORDER BY unit_price ASC;

ASC is usually the default, but writing it can clarify intent. DESC reverses the direction.

SELECT product_id, product_name, unit_priceFROM productORDER BY unit_price DESC;

Without ORDER BY, order is unspecified

Multiple sort keys are evaluated left to right

SELECT    category,    product_name,    unit_priceFROM productORDER BY    category ASC,    unit_price DESC,    product_id ASC;

The first key forms broad groups. The second orders rows within equal first-key values. The final unique key breaks any remaining ties.

Deterministic sorting requires a total tie-breaker

SELECT sale_id, customer_id, sold_atFROM saleORDER BY sold_at DESC;

Two sales share the timestamp 2026-08-05 09:00:00. Their relative order is unspecified.

SELECT sale_id, customer_id, sold_atFROM saleORDER BY    sold_at DESC,    sale_id DESC;

A unique final key makes the requested sequence total: every pair of rows can be ordered.

Sort by output aliases

SELECT    s.sale_id,    s.quantity * p.unit_price * (1 - s.discount_rate)        AS net_amountFROM sale AS sJOIN product AS p  ON p.product_id = s.product_idORDER BY    net_amount DESC,    s.sale_id ASC;

Most SQL engines allow output aliases in ORDER BY. This is useful because ordering is logically applied after the select list is formed.

Avoid fragile ordinal positions

SELECT    product_name,    category,    unit_priceFROM productORDER BY 3 DESC, 1 ASC;

3 means the third selected expression and 1 means the first. Reordering the select list silently changes the sort. Prefer names or expressions in maintained SQL.

ORDER BY    unit_price DESC,    product_name ASC;

NULL placement is not portable by default

SELECT customer_id, full_name, cityFROM customerORDER BY city ASC, customer_id ASC;

Different engines place NULL values differently for ascending and descending sorts. Some support NULLS FIRST and NULLS LAST; SQL Server and MySQL require alternative expressions for some needs.

SELECT customer_id, full_name, cityFROM customerORDER BY    city IS NULL ASC,    city ASC,    customer_id ASC;

The Boolean-like expression is 0 for non-NULL cities and 1 for NULL cities in SQLite, so present values sort first.

Collation controls text ordering

SELECT product_id, product_nameFROM productORDER BY    product_name COLLATE NOCASE ASC,    product_id ASC;

Alphabetical order depends on collation: case, accents, language conventions, punctuation, and Unicode normalization can matter. Production systems should choose a collation that matches domain and locale requirements.

Expressions can define business order

SELECT    product_id,    product_name,    category,    unit_priceFROM productORDER BY    CASE category        WHEN 'course' THEN 1        WHEN 'lab'    THEN 2        WHEN 'book'   THEN 3        ELSE 4    END,    unit_price DESC,    product_id ASC;

Business ranking should be explicit rather than relying on accidental alphabetical order.

ORDER BY operates on the result, not storage

Practice lab

1. List products from cheapest to most expensive, breaking equal prices by SKU.
2. List sales newest first with deterministic tie handling.
3. List customers by city with NULL cities last.
4. Rank sales by calculated net amount descending.
5. Place courses first, labs second, and books third.

SELECT sku, product_name, unit_priceFROM productORDER BY unit_price ASC, sku ASC;SELECT sale_id, customer_id, sold_atFROM saleORDER BY sold_at DESC, sale_id DESC;SELECT customer_id, full_name, cityFROM customerORDER BY city IS NULL ASC, city ASC, customer_id ASC;SELECT s.sale_id,       s.quantity * p.unit_price * (1 - s.discount_rate) AS net_amountFROM sale AS sJOIN product AS p ON p.product_id = s.product_idORDER BY net_amount DESC, s.sale_id ASC;SELECT product_id, product_name, categoryFROM productORDER BY CASE category           WHEN 'course' THEN 1           WHEN 'lab' THEN 2           WHEN 'book' THEN 3           ELSE 4         END,         product_id ASC;

Common failures

Assuming primary-key or insertion order

Only ORDER BY creates an ordering contract.

Sorting on a non-unique key alone

Tied rows can swap positions between executions or pages.

Using column ordinals

Select-list edits can silently alter ordering.

Ignoring NULL and collation behavior

Default placement and text rules differ across engines.

Confusing display order with storage

The clause affects the result of this query, not physical table organization.

Summary and references

• ORDER BY is the only reliable way to request row order.
• Multi-key sorts are evaluated from left to right.
• Add a unique final key for deterministic results.
• Prefer aliases or names over ordinal positions.
• Specify NULL placement and collation when they matter.

References

• SQLite: ORDER BY clause
• SQLite: collating sequences
• PostgreSQL: sorting rows