05 — ORDER BY

One line: ORDER BY sorts your result rows — alphabetically for text, numerically for numbers — ascending or descending.

The idea in plain English

By default the database returns rows in whatever order is fastest, which can differ between machines/runs. If you want a guaranteed order, you must ask for it with ORDER BY.

"Give me the customers, sorted A→Z by first name."

Syntax & where it goes

SELECT columns
FROM   table
WHERE  condition          -- filter first
ORDER BY column [ASC|DESC];  -- sort last

ORDER BY sits at the end of the query and is one of the last things SQL does — it selects and filters first, then sorts what's left. Mental order of operations:

FROM → WHERE → SELECT → ORDER BY

• ASC = ascending (A→Z, low→high) — this is the default if you write nothing.
• DESC = descending (Z→A, high→low).

SELECT first_name FROM customer ORDER BY first_name;       -- A→Z (default)
SELECT first_name FROM customer ORDER BY first_name ASC;    -- same, but explicit
SELECT first_name FROM customer ORDER BY first_name DESC;   -- Z→A

Writing ASC explicitly changes nothing at runtime, but it tells the next reader "I meant ascending on purpose." Small clarity win.

Sorting by multiple columns

Useful when the first column has duplicates. SQL sorts by the first column, then breaks ties with the second, and so on.

SELECT company, name, sales
FROM   table
ORDER BY company, sales;

Reads as: sort by company first (Apple, Google, Xerox…), then within each company, sort by sales. So Apple's people come first, and among Apple's people they're ordered by sales.

Each column gets its own direction:

SELECT store_id, first_name, last_name
FROM   customer
ORDER BY store_id DESC, first_name ASC;
-- store 2 group first; within each store, names A→Z

The order of columns = priority. ORDER BY a, b is primary sort a, tiebreaker b — not the same as ORDER BY b, a.

Production notes

• ORDER BY + LIMIT = "top N." Sorting then keeping the first few rows is the way to get "top 5 highest payments," "most recent orders," etc. (LIMIT is the next lesson.)
• Sorting is expensive. On large tables, ORDER BY forces a sort of the result set. Sorting on an indexed column is much cheaper — worth knowing when queries get slow.
• Where do NULLs go? ⚠️ In Postgres, NULLs sort last in ASC and first in DESC by default. You can control it explicitly: ORDER BY col ASC NULLS FIRST. This differs across databases (MySQL treats NULL as smallest, so NULLs come first in ASC) — don't assume, especially in portable code.
• You can sort by a column you didn't SELECT. SELECT first_name, last_name ... ORDER BY store_id is valid. It works, but usually you should select the column you sort by so the result makes visual sense to whoever reads it.
• ORDER BY 1, 2 (by position) exists but is fragile. You can sort by the position number of a SELECT column (ORDER BY 1 = first selected column). It's terse, but if someone reorders your SELECT list the sort silently changes. Prefer naming the column.
• Text sort depends on collation. Whether uppercase sorts before lowercase (and how accents sort) depends on the database's collation setting — occasionally surprising when "Zebra" and "apple" don't sort where you expect.

Challenges

All use the DVD Rental database.

Challenge 1 — Warm-up

Return store_id, first_name, last_name from customer, sorted by first_name Z→A.

SELECT store_id, first_name, last_name
FROM   customer
ORDER BY first_name DESC;

Challenge 2 — Multi-column, mixed direction

From the payment table, list customer_id and amount. Group the rows by customer (lowest customer_id first), and within each customer show their biggest payments first.

SELECT customer_id, amount
FROM   payment
ORDER BY customer_id ASC, amount DESC;

Challenge 3 — Spot the issue 🔎

A teammate wants films grouped by rating, and within each rating listed alphabetically by title. They write:

SELECT title, rating
FROM   film
ORDER BY title, rating;

The output isn't grouped by rating at all. Why, and what's the fix?

SELECT title, rating
FROM   film
ORDER BY rating, title;

30-second recap