The Dearest Three

A table has no order

This is the idea the whole exercise rests on, and it surprises almost everyone: the rows in a table are not in an order. A small result usually comes back looking like the order you inserted it in, which makes it very tempting to rely on. The database never promised that, and it is free to return the same rows in a different order once there is an index to use or once the table is big enough to read differently.

So "the newest post", "the top three scores" and "the cheapest option" are all questions about order, and all of them need you to say what the order is.

CREATE TABLE birds (id INTEGER PRIMARY KEY, species TEXT, seen INTEGER);
INSERT INTO birds (species, seen) VALUES ('wren', 12), ('heron', 1), ('moorhen', 9), ('dunnock', 3);

SELECT species, seen FROM birds ORDER BY seen;

SELECT species, seen FROM birds ORDER BY seen DESC;

species | seen
--------+-----
heron   |    1
dunnock |    3
moorhen |    9
wren    |   12
(4 rows)

species | seen
--------+-----
wren    |   12
moorhen |    9
dunnock |    3
heron   |    1
(4 rows)

ASC (ascending, smallest first) is the default and rarely written. DESC reverses it, and it applies only to the column it follows — in ORDER BY shelf, deposit DESC the shelves are ascending and the deposits within each shelf are descending.

LIMIT takes the first few of whatever order is in force

LIMIT n returns at most n rows. That is all it does: it has no opinion about which rows are interesting, so it simply takes them from the top of the result as ordered.

CREATE TABLE birds (id INTEGER PRIMARY KEY, species TEXT, seen INTEGER);
INSERT INTO birds (species, seen) VALUES ('wren', 12), ('heron', 1), ('moorhen', 9), ('dunnock', 3);

SELECT species, seen FROM birds LIMIT 2;

SELECT species, seen FROM birds ORDER BY seen DESC LIMIT 2;

SELECT species, seen FROM birds ORDER BY seen DESC LIMIT 2 OFFSET 2;

species | seen
--------+-----
wren    |   12
heron   |    1
(2 rows)

species | seen
--------+-----
wren    |   12
moorhen |    9
(2 rows)

species | seen
--------+-----
dunnock |    3
heron   |    1
(2 rows)

The first is two arbitrary rows. The second is genuinely the top two. The third skips those two and returns the next two, which is how page two of a listing is fetched.

LIMIT and OFFSET are spelled this way in SQLite, PostgreSQL and MySQL. Microsoft SQL Server uses TOP or OFFSET ... FETCH NEXT ... ROWS ONLY instead, so it is one clause to check if you ever move a query between engines.

One more thing: ties

If two rows tie on the column you sorted by, the order between them is undefined — and a LIMIT then picks one of them for reasons you cannot see. When that matters, add a second sort key that cannot tie, usually the primary key or a name: ORDER BY deposit DESC, name. The seven deposits in this exercise are all different, so you do not need it here, but the habit costs nothing.

Your turn

The editor holds a seven-row tools table and a query that is meant to show the three biggest deposits, dearest first.

Press Run first. Three rows come back — the hedge trimmer, the cordless drill and the wheelbarrow — and a glance at the full table shows the 500 has no business in a top three.

Add the missing clause between FROM tools and LIMIT 3 so the rows are sorted by deposit, largest first, before the limit takes three. Run it again and you should see 1500, 1200, 1100.

Then change LIMIT 3 to LIMIT 3 OFFSET 3 to see the next three down.

If something goes wrong

If you get near "ORDER": syntax error, the new clause is after the LIMIT. Move it above.

If you get the three cheapest tools, the sort is still ascending and wants DESC after the column name.

If you get seven rows, the LIMIT 3 has gone missing.

Nothing here can break. Every run builds the table from scratch and throws it away afterwards.