Sorting with ORDER BY

Without ORDER BY, a database is free to return rows in whatever order is most convenient for it internally — usually something close to storage order, but this is never guaranteed by the SQL standard and can change between queries. ORDER BY gives you an explicit, reliable sort.

SELECT name, signup_date FROM users ORDER BY signup_date;

Ascending and descending

Sorting defaults to ascending (ASC) — smallest to largest, earliest to latest, A to Z. DESC reverses it:

SELECT name, signup_date FROM users ORDER BY signup_date DESC; -- newest first

Sorting by multiple columns

Listing more than one column sorts by the first, then uses the next column only to break ties within groups that share the same value in the first:

SELECT name, country, signup_date FROM users
ORDER BY country ASC, signup_date DESC;

This sorts all rows by country alphabetically, and within each country, by signup_date newest-first. Each column in an ORDER BY list can have its own independent direction.

Sorting by an alias or column position

You can sort by a column alias defined in the SELECT list:

SELECT name, price * quantity AS total FROM order_items ORDER BY total DESC;

You can also sort by a column's position number in the SELECT list (ORDER BY 2 sorts by the second selected column), which works but is fragile — reordering the SELECT list silently changes what you're sorting by. Sorting by name or alias is almost always clearer and safer.

Sorting by an expression

ORDER BY isn't limited to plain column names — any expression works, including a CASE expression (covered in a later lesson) for custom sort orders that don't correspond to a simple ascending or descending value.

SELECT name, status FROM orders
ORDER BY
  CASE status
    WHEN 'pending' THEN 1
    WHEN 'shipped' THEN 2
    WHEN 'delivered' THEN 3
  END;

With filtering and sorting covered, the next lesson looks at LIMIT, for controlling how many rows come back — the basis of pagination.