All 22 modules are open from the start — nothing here is
locked, and nothing costs anything. Sign in so your progress, titles and
credentials stay with you, on every device you use.
Free forever, with your Google account. No password, no payment.
Deciding what comes first, and how much comes back
A table has no order. Whatever sequence your rows arrive in without an
ORDER BY is an accident of how the data happens to be stored,
and it can change without warning. This module is about taking control of
that — and about the fact that "top 10" is meaningless until you have.
Ready?
1
Rows Are a Set, Not a List
A table is a set of rows. Sets have no order. When you
run SELECT * FROM users and the rows come back by
user_id, that is a coincidence of how this small table is
stored — not a promise, and not something to build on.
The coincidence holds until it does not. Add an index, delete some rows
and insert new ones, run the same query on a bigger machine that reads in
parallel, and the order changes. The bug that follows is a nasty one,
because the query did not change and nothing errored.
SELECT name, signup_date
FROM users
ORDER BY signup_date;
ORDER BY is the last clause in the query and the last thing
to run. Ascending is the default: smallest number first, earliest date
first, A before Z.
The rule in one line
If a human will read the result, it needs an ORDER BY. "It
came out sorted when I tested it" is an observation, not a guarantee,
and it is not one you can put in a report.
Quick check
A query with no ORDER BY has returned rows in id order every day for a year. Is that order guaranteed?
2
Direction Attaches to a Column
DESC reverses a sort; ASC is the default and
can be written out for clarity. The important detail is that the keyword
applies to the one column it follows, not to the whole
clause.
ORDER BY plan, name DESC -- plan ascending, name descending
ORDER BY plan DESC, name DESC -- both descending
The first of those is a very common accident. Someone wants both columns
reversed, writes DESC once at the end, and gets a result
that is half right — and half right is the hardest kind of wrong to
notice.
Sorting works on whatever the column holds. Text sorts alphabetically,
numbers numerically, and dates chronologically provided they are
stored sensibly. This database stores dates as text in
YYYY-MM-DD form precisely because that format sorts
correctly as text; 14/01/2023 would not.
Quick check
What does ORDER BY category, price DESC do?
3
A Second Column Only Breaks Ties
List several columns and the database sorts by the first, then uses the
second only where the first is equal, then the third where both are
equal. Exactly like a phone book: surname first, forename only when two
people share a surname.
SELECT name, plan
FROM users
ORDER BY plan, name;
This matters more than it sounds. Sorting only by a column with few
distinct values — a plan, a status, a country — leaves large groups of
rows tied, and tied rows are in arbitrary order. Your
report looks sorted, and the rows inside each plan can still shuffle
between runs.
Where this bites
Paginating with ORDER BY plan LIMIT 20 OFFSET 20 when
plan has three values means the database is free to order the ties
differently for page one and page two. Rows appear on both pages,
other rows appear on neither, and nobody can reproduce it.
A sort used for pagination must end in something unique, usually the id.
Quick check
Twelve users sorted by ORDER BY plan alone. What is guaranteed about the five free users?
4
Taking a Slice
LIMIT caps how many rows come back. It is applied
after the sort, and that ordering is the entire reason
"top 5" means anything.
SELECT order_id, amount
FROM orders
ORDER BY amount DESC
LIMIT 5;
Drop the ORDER BY and you still get five rows — just five
arbitrary ones. That is a genuinely useful thing when you want a peek at
an unfamiliar table, and a genuinely wrong thing when someone asked for
the biggest orders.
OFFSET throws rows away before LIMIT takes its
slice, which turns the pair into a window you can slide down a sorted
list.
LIMIT 3 OFFSET 1 -- skip the winner, take the next three
LIMIT 20 OFFSET 40 -- page 3, at 20 rows per page
Use it as a seatbelt too
When you are exploring a table you do not know the size of, put
LIMIT 100 on the end of every query. It costs nothing when
the table is small and saves you from pulling back ten million rows
when it is not.
Quick check
Which query returns the three most expensive products?
0 of 4 completed
Loading the tables…
01
To do
A table has no order. Rows come back in whatever sequence the database
finds convenient, and that sequence can change without warning. If the
order matters, you have to say so.
SELECT name, signup_date FROM users ORDER BY signup_date;
ORDER BY goes last, after WHERE. Ascending is
the default: smallest number first, earliest date first, A before Z.
Your task: return name and
signup_date for every user, oldest signup first.
query.sql
PostgreSQLCtrl↵ to run
Hint
ORDER BY signup_date — dates in this database are stored as text in YYYY-MM-DD form, which sorts correctly as text precisely because of that ordering.
Output
02
To do
Add DESC to reverse a sort. It applies to the one column it
follows, not to the whole clause — which matters as soon as you sort on
two things.
ORDER BY price DESC
ASC exists too, and is the default. Writing it out costs
nothing and removes any doubt for the next reader.
Your task: return name and
price from products, most expensive first.
query.sql
PostgreSQLCtrl↵ to run
Hint
ORDER BY price DESC — the keyword goes after the column name, not before it.
Output
03
To do
List several columns and the database sorts by the first, then uses the
second only to break ties, then the third, and so on. That is exactly
how a phone book works: surname first, first name only when two people
share a surname.
ORDER BY plan, name
Each column carries its own direction. ORDER BY plan, name
DESC sorts plan ascending and only the names descending — a
common and quiet source of wrong answers.
Your task: return name, plan
and country, sorted by plan and then by
name, both ascending.
query.sql
PostgreSQLCtrl↵ to run
Hint
ORDER BY plan, name — two columns, comma separated, no direction keywords needed since ascending is the default.
Output
04
To do
LIMIT cuts the result to the first N rows. It is applied
after the sort, which is the only reason "top 5" means
anything: without an ORDER BY, a LIMIT gives
you five arbitrary rows rather than the five biggest.
ORDER BY amount DESC LIMIT 5;
Your task: return the order_id and
amount of the five largest orders, largest first.
query.sql
PostgreSQLCtrl↵ to run
Hint
Two lines: ORDER BY amount DESC, then LIMIT 5. The sort has to come first or the limit picks the wrong five.
Output
The runners-up
To do
Marketing is writing a page about the products that are expensive but not
the flagship. They want the second, third and fourth most
expensive products — skipping the priciest one entirely.
OFFSET is how you skip. LIMIT 3 OFFSET 1 throws
away the first row and returns the next three, so it pairs with
ORDER BY to take any window out of a sorted list.
Your task: return name and
price from products — three rows, most expensive
of the three first, with the single priciest product excluded.
query.sql
PostgreSQLCtrl↵ to run
Hint
ORDER BY price DESC, then LIMIT 3 OFFSET 1. OFFSET counts rows to throw away, so 1 skips exactly the top row.