ORDER BY and LIMIT

A SQL result has no order of its own. This lesson adds the two clauses that decide what comes out first and how much comes out at all: ORDER BY, which sorts the rows, and LIMIT, which keeps only the first few of them.

Sorting with ORDER BY

ORDER BY goes after FROM (and after WHERE, when there is one) and names the column to sort on:

CREATE TABLE scores (player TEXT, points INTEGER);
INSERT INTO scores VALUES ('mira', 40);
INSERT INTO scores VALUES ('ravi', 55);
INSERT INTO scores VALUES ('anya', 40);
INSERT INTO scores VALUES ('tomas', 31);

SELECT player, points FROM scores ORDER BY points;

tomas|31
mira|40
anya|40
ravi|55

Ascending is the default: smallest number first, A before Z for text. ASC says that out loud, DESC reverses it. The sort key does not have to be a column you selected - sorting by points while selecting only player is normal and common.

Ties need a second key

Look at the two rows on 40 points. Sorting the same table with DESC gives:

ravi|55
mira|40
anya|40
tomas|31

mira before anya is not a rule. It is whatever the engine found convenient this time, and it can change when the data grows or the query plan changes. A comma and a second key settle it for good:

SELECT player, points FROM scores ORDER BY points DESC, player ASC;

ravi|55
anya|40
mira|40
tomas|31

"Most points first; equal points alphabetically." Any sort whose first key can repeat wants a second key.

A sort is only as honest as the column's type

Numbers kept in a TEXT column sort like text, character by character:

CREATE TABLE sizes (label TEXT, bytes TEXT);   -- bytes should have been INTEGER
INSERT INTO sizes VALUES ('log', '9');
INSERT INTO sizes VALUES ('image', '1024');
INSERT INTO sizes VALUES ('note', '85');

SELECT label, bytes FROM sizes ORDER BY bytes;

image|1024
note|85
log|9

'1024' lands first because '1' comes before '8'. Declare that column INTEGER and the identical query returns log|9, note|85, image|1024. When a numeric sort looks insane, check the column's type before you doubt the sort.

LIMIT keeps the first rows

LIMIT n cuts the result down to n rows, and it runs after the sort - that is the whole trick:

SELECT player FROM scores ORDER BY points DESC LIMIT 2;

ravi
mira

LIMIT 2 OFFSET 2 skips two sorted rows and then takes two, returning anya and tomas - page two of a two-per-page list, which is how pagination works everywhere. The clause order is fixed grammar:

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...;

WHERE drops rows, ORDER BY sorts what survived, LIMIT trims the sorted list.

Common mistakes

• Leaving the direction out when you want the biggest. Ascending is the default, so a LIMIT after it hands back the smallest rows - in the scores table above, ORDER BY points LIMIT 2 returns tomas and mira. Plausible names, wrong end of the table, no error to warn you.
• LIMIT with no ORDER BY. "The first three rows" is then whatever the engine felt like producing; on a small table that is usually insert order, which is a habit, not a promise.
• Sorting by the table name. ORDER BY scores stops the query with Error: no such column: scores - the table is scores, the columns are player and points.
• Putting a direction where a key belongs. ORDER BY DESC is not a syntax error, which is what makes it confusing: SQLite looks for a column called DESC and reports no such column: DESC.

Your exercise

The mountains table is already written in the editor; you add the single query that answers the question, and nothing else. Say the requirement as a sentence first, then name the sort key and its direction explicitly - the mistake this exercise catches most often is a query that sorts the right column the wrong way and quietly returns the short peaks.