Lesson Beginner

ORDER BY

Tip

Rows in a SQL table have no natural order. If you need a specific order, you have to ask for it.

ORDER BY is how you sort a result set.
Without it, the database is free to return rows in whatever order is cheapest, and that order is not stable across runs, indexes, or versions.

ASC and DESC #

ORDER BY col ASC sorts ascending (smallest first). DESC sorts descending. ASC is the default, so the two queries below are equivalent:

sql

SELECT id, total FROM orders ORDER BY total;
SELECT id, total FROM orders ORDER BY total ASC;

Multiple Columns: Tiebreakers #

Order by more than one column to break ties. Earlier columns win; later columns only matter when earlier ones are equal.

sql

SELECT id, status, total
FROM orders
ORDER BY status ASC, total DESC;

Rows sort by status first, and within each status the rows sort by total largest-to-smallest. Each column can have its own ASC/DESC direction.

Order by Alias or Position #

You can sort by a column alias defined in SELECT, because ORDER BY runs after SELECT:

sql

SELECT
  id,
  total * 1.1 AS total_with_tax
FROM orders
ORDER BY total_with_tax DESC;

You can also sort by position number: ORDER BY 2 means the second column in the SELECT list. This is compact, but fragile. If someone reorders the SELECT list, the sort changes silently. Use named columns or aliases in any query that will be saved.

NULLs When Sorting #

NULL values have no natural position in an order. SQL dialects differ on where they land by default. You can control it explicitly with NULLS FIRST or NULLS LAST.

sql

SELECT id, total
FROM orders
ORDER BY total DESC NULLS LAST;

Warning

Text sorting is not numeric sorting. A VARCHAR column sorts lexicographically, so '10' comes before '2'. If a number-shaped column is stored as text, ORDER BY will not give you the order you expect. Cast to a numeric type first, or fix the column type.

Try It #

Sort the orders table by status first, then by total within each status.

Loading SQL editor...

Mistakes to Watch For #

Relying on row order without an ORDER BY. "It worked last time" is not a guarantee.
Sorting a number-shaped text column and getting alphabetical order.
Forgetting that each sort key has its own direction: ORDER BY a, b DESC sorts a ascending and b descending, not both descending.
Sorting by position number in a query that will be read or edited later.

Practice #

Return id and total from orders, sorted from smallest total to largest. When two orders have the same total, show the lower id first.

Loading SQL editor...

Knowledge check #

4 questions

0 / 4 answered

ORDER BY status, created_at DESC sorts the result by:
Without ORDER BY, the row order in a SQL result is:
In ORDER BY 2, the 2 refers to:
Default behavior for NULL values in ORDER BY differs by engine. To be explicit and portable on modern engines:

Next Step #

Continue to LIMIT / OFFSET to cap result size and paginate through sorted rows.