Level 1 · Price list, most expensive first

ORDER BY sorts the result. It goes last, after FROM and WHERE:

SELECT name, unit_price
FROM products
ORDER BY unit_price DESC;

ASC (ascending, the default) sorts low to high, A to Z, oldest to newest. DESC sorts the other way. Each sort column gets its own direction.

Tie-breakers

Several products can share the same price. With only ORDER BY unit_price DESC, the database may put tied products in any order, and that order can change from one run to the next or between your laptop and production. For a report, that means a different list each time someone refreshes it. Add a second column to decide ties:

ORDER BY unit_price DESC, name ASC

The second column only matters when the first is tied. A good final tie-breaker is a unique column, such as a primary key (product_id), because then the order is fully determined. Analysts call this a deterministic sort.

Sorting by things you did not select

You can sort by any column of the table, even one not in the SELECT list, and by expressions:

SELECT name
FROM products
ORDER BY unit_price - unit_cost DESC;

You can also sort by an alias from the SELECT list. ORDER BY is logically evaluated after SELECT, so aliases are visible there in every major database, unlike in WHERE:

SELECT name, unit_price * 0.9 AS sale_price
FROM products
ORDER BY sale_price DESC;

Sorting by position

ORDER BY 2 DESC sorts by the second selected column. It is handy for quick exploration and supported in Postgres, DuckDB, MySQL and BigQuery, but it breaks silently when someone inserts a column into the SELECT list. Use names in anything you save.

Text sorts by character codes

Text is sorted alphabetically, but the exact rules (called a collation) depend on the database. Uppercase and lowercase may sort apart, so 'oak' can land after 'Willow'. When display order of names matters, sort by LOWER(name).

Common mistakes

• Putting ORDER BY before WHERE. The clause order is fixed: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.
• Sorting text that holds numbers. If a price were stored as text, '100' would sort before '9'. Check column types when a sort looks strange.
• Assuming a table “is already sorted”. Tables have no inherent order. Only ORDER BY guarantees one.