Step-by-step lessons

Order and Size

Four short lessons: why unsorted results are not stable, how to reverse a sort, what a second sort column is actually for, and how LIMIT and OFFSET take a slice out of a sorted list.

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?

Write it yourself

Query Lab

Four queries about the order rows come back in, and how to take just the top of a list. Here the row order is part of the answer, so the checks compare position by position.

0 of 4 cleared

Real SQLite runs right here in your browser — nothing to install, nothing sent to a server. The database is rebuilt from scratch before every single run, so nothing you write can break it and nothing carries over between exercises.

The Northwind Analytics database A small SaaS product's database: who signed up, what they subscribed to, what they bought, and what they did in the app.

Loading the tables…

01

Put the rows in an order

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

SELECT name, signup_date
FROM users

SQLite

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

Largest first

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

SELECT name, price
FROM products
ORDER BY

SQLite

Hint

ORDER BY price DESC — the keyword goes after the column name, not before it.

Output

03

Sort on two columns

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

SELECT name, plan, country
FROM users
ORDER BY

SQLite

Hint

ORDER BY plan, name — two columns, comma separated, no direction keywords needed since ascending is the default.

Output

04

Just the top of the list

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

SELECT order_id, amount
FROM orders

SQLite

Hint

Two lines: ORDER BY amount DESC, then LIMIT 5. The sort has to come first or the limit picks the wrong five.

Output

Boss round

Assignment: The Runners-Up

One graded query. Sorting and a window into the middle of the list — pass every check and the stage is cleared.

This one is graded. You can see 2 of the checks up front, and 2 stay hidden until you run. The hidden ones test the same job from angles you have not been shown, so code that genuinely solves the problem clears this and code shaped around the visible examples does not. Pass them all and the stage is yours.

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

-- Sort, skip one, take three.
SELECT name, price
FROM products

SQLite

Hint

ORDER BY price DESC, then LIMIT 3 OFFSET 1. OFFSET counts rows to throw away, so 1 skips exactly the top row.

Output

Lock it in

Guess the Term

Read the clues and name the concept. The fewer clues you need, the more points you score.

Round 1 Score 0

Keep it handy

ORDER BY & LIMIT Quick Reference

The whole stage on one screen.

Sorting

1

`ORDER BY col`

Ascending. Last clause in the query.

2

`ORDER BY col DESC`

Descending. The keyword attaches to that one column.

3

`ORDER BY a, b`

b only breaks ties on a.

Slicing

`LIMIT n`

First n rows, after the sort.

`OFFSET n`

Throw away n rows first.

Pagination

Page p at size s is LIMIT s OFFSET (p-1)*s.

Traps

1

LIMIT with no sort

Arbitrary rows, not the top ones.

2

One DESC, two columns

Only the column before it is reversed.

3

Unstable pagination

Ties shuffle between pages. End the sort on something unique.

Clause order

1

FROM

Which table.

2

WHERE

Which rows.

3

SELECT

Which columns.

4

ORDER BY, then LIMIT

Sort, then cut. Always that way round.

Sign in to train

Not unlocked yet

Sorting & Limiting

Order and Size

Rows Are a Set, Not a List

The rule in one line

Quick check

Direction Attaches to a Column

Quick check

A Second Column Only Breaks Ties

Where this bites

Quick check

Taking a Slice

Use it as a seatbelt too

Quick check

Query Lab

Put the rows in an order

Largest first

Sort on two columns

Just the top of the list

Assignment: The Runners-Up

The runners-up

Guess the Term

ORDER BY & LIMIT Quick Reference

Sorting

ORDER BY col

ORDER BY col DESC

ORDER BY a, b

Slicing

LIMIT n

OFFSET n

Pagination

Traps

LIMIT with no sort

One DESC, two columns

Unstable pagination

Clause order

FROM

WHERE

SELECT

ORDER BY, then LIMIT

Notification

`ORDER BY col`

`ORDER BY col DESC`

`ORDER BY a, b`

`LIMIT n`

`OFFSET n`