LE PETIT JOURNAL
THE CLERK'S HANDBOOK

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 (a clause is one part of a query, named after the keyword that starts it).

ORDER BY comes after WHERE and names one or more columns to sort by, the sort keys. 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;
nameprice
Cinnamon roll2.6
Almond croissant2.2
Brioche2.1
Apple turnover1.9
Pain au chocolat1.6
Hot cross bun1.5
Croissant1.4

Several sort keys

Two rows tie when they hold the same value in the column you sort by. 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;
categorynameprice
breadSourdough loaf4.5
breadWalnut bread4.2
breadRye loaf3.8
breadSeeded rolls3.8
breadFocaccia3.6
cakeRaspberry tart4.1
cakeLemon tart3.9
cakeCheesecake slice3.8
cakeCarrot cake slice3.2

You can sort by a column that you do not display, by a calculation such as price * 2, or by an alias, a name given to a column with AS 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 and drops the rest. On its own it keeps whichever n rows the database comes across first, 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;
idcustomer_iddaytime
30322026-03-2917:45
30272026-03-2916:40
30192026-03-2916:00

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;
nameprice
Hot cross bun1.5
Pain au chocolat1.6

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

Your turn. Show the name and price of the three dearest products, dearest first. · Expected: 3 rows.

Solution
SELECT name, price
FROM product
ORDER BY price DESC
LIMIT 3;
nameprice
Sourdough loaf4.5
Walnut bread4.2
Raspberry tart4.1

Ties and LIMIT

Take care when two rows can tie right where LIMIT cuts the list: the last row kept and the first row dropped may hold the same value. 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 (a NULL marks a missing value, see the NULL page) 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;
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list DISTINCT removes repeated rows before sorting, so price is gone by then: sort by a column you selected, or use GROUP BY and sort by an aggregate such as MIN(price).
SELECT category
FROM product
GROUP BY category
ORDER BY MIN(price);
category
bread
pastry
cake

GROUP BY also keeps one row per category, and it lets you sort the categories by an aggregate, a figure worked out over all the rows of a group, here the price of 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;
ERROR: LIMIT #,# syntax is not supported LIMIT 2, 3 is not standard SQL: write LIMIT 3 OFFSET 2 (the row count first, then how many rows to skip).
SELECT name, price
FROM product
ORDER BY price DESC, id
LIMIT 3 OFFSET 2;
nameprice
Raspberry tart4.1
Lemon tart3.9
Rye loaf3.8

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.