ORDER BY and LIMIT

Game: chapter II

A table has no natural order. If the order of the rows matters to you, you must ask for it.

Why rows need sorting

A beginner often assumes that rows come back in the order they were inserted. Sometimes they do, by accident, but the SQL standard makes no such promise, and a database is free to return rows in whatever order is fastest for it. The moment a question contains a word like "cheapest", "latest", "first" or "top three", the query needs an ORDER BY clause.

ORDER BY comes after WHERE and names one or more columns. Each column sorts ascending by default (ASC: smallest number, earliest date, A before Z); add DESC after a column to reverse it. The examples use the bakery database from the bakery database page.

SELECT name, price
FROM product
WHERE category = 'pastry'
ORDER BY price DESC;

Several sort keys

When two rows tie on the first column, the second column decides, then the third, and so on. Each column carries its own direction, so you can sort by category alphabetically and, inside each category, by price from the most to the least expensive, with the name as a third key for the two breads that cost the same. The example keeps the products of 3 euros and more. The database never looks at the second key unless the first one ties.

SELECT category, name, price
FROM product
WHERE price >= 3
ORDER BY category, price DESC, name;

You can sort by a column that you do not display, and by a computed expression or an alias defined in SELECT. Sorting happens after the rows have been chosen, which is why aliases work here while they do not in WHERE (see Order of execution).

Keeping the first rows: LIMIT

LIMIT n, written at the very end of the query, keeps only the first n rows of the result. On its own it keeps an arbitrary n rows, which is rarely useful; combined with ORDER BY it answers questions such as "the three most recent orders" or "the cheapest pastry". OFFSET k skips the first k rows before counting, which is how a website shows page two of a list.

SELECT id, customer_id, day, time
FROM orders
ORDER BY day DESC, time DESC
LIMIT 3;

Here the second key matters: sixteen orders were placed on the last day, Sunday 29 March, and only the time tells them apart. Without time DESC, the database would be free to pick any of them in any order.

SELECT name, price
FROM product
ORDER BY price
LIMIT 2 OFFSET 2;

These are the third and fourth cheapest products: the two cheapest are skipped, the next two are kept.

Ties and LIMIT

Be careful when the value you sort by can tie at the edge of the limit. If two products both cost the same and you ask for LIMIT 1, you get one of them, and which one is not defined. In the bakery, the Rye loaf, the Seeded rolls and the Cheesecake slice all cost 3.80, and the two eclairs 2.80. When the question is "which product is the cheapest", it is worth checking whether the second row has the same price as the first: drop the limit, or raise it to five, and read the top of the list with your own eyes. Adding a second sort key, such as the name or the id, at least makes the answer the same every time you run it.

NULL values also need a thought: in SQLite they sort before every other value in ascending order, and after them in descending order. PostgreSQL does the opposite (last in ascending order, first in descending); write NULLS FIRST or NULLS LAST after the sort key to say which you want, and both agree. If a column can be missing, the top of a sorted list may be full of NULLs rather than of the smallest real values; filter them out with WHERE ... IS NOT NULL when they do not belong in the answer.

Grammar: DISTINCT and ORDER BY

A query with a red edge is one the game's terminal refuses: the answer under it is the terminal's own, and the query after it is the correct version.

SELECT DISTINCT removes repeated rows before the sort, so the sort can only use what was selected.

SELECT DISTINCT category
FROM product
ORDER BY price;

SELECT category
FROM product
GROUP BY category
ORDER BY MIN(price);

GROUP BY also keeps one row per category, and it lets you sort the categories by an aggregate, here their cheapest product (see GROUP BY).

Grammar: LIMIT and OFFSET

Skipping rows is written with OFFSET, after the row count. The short form with a comma, which puts the skipped rows first, is not standard SQL.

SELECT name, price
FROM product
ORDER BY price DESC, id
LIMIT 2, 3;

SELECT name, price
FROM product
ORDER BY price DESC, id
LIMIT 3 OFFSET 2;

Order of clauses

The clauses always come in the same order: SELECT, FROM, WHERE, then ORDER BY, then LIMIT. Putting LIMIT before ORDER BY is a syntax error, and writing ORDER BY before WHERE is too. Later pages add GROUP BY and HAVING, which sit between WHERE and ORDER BY.

Common mistakes

Relying on the insertion order or on the id as a stand-in for time: the row with the highest id is not always the latest event, so sort by the date itself. Forgetting DESC and reading the bottom of a list as if it were the top. Sorting dates stored in a form that does not sort as text, such as day/month/year. Using LIMIT 1 without checking for ties. Sorting by a number stored as text, where '10' comes before '9'.

Try it: The cheap shelf, on the exercises page.

In the game: Chapter II, The Neighbouring Suite, practises ORDER BY and LIMIT.