Reference · module 3
Sorting and functions
Everything this module explains, on one page.
ORDER BY
Sort a result and answer top-N questions.
- ORDER BY sorts the result: SELECT title FROM books ORDER BY year; lists books from oldest to newest. Ascending is the default, and ASC says it explicitly.
- DESC reverses the order: ORDER BY price DESC puts the most expensive first. Several columns sort in turn: ORDER BY author, year sorts by author, then by year within each author.
- ORDER BY with LIMIT answers top questions: ORDER BY price DESC LIMIT 3 gives the three most expensive rows. ORDER BY always comes before LIMIT.
The mistake you are about to make
SELECT name FROM products LIMIT 3 ORDER BY price DESC;
SELECT name FROM products ORDER BY price DESC LIMIT 3;
In English you say the three first and the most expensive second. SQL's order is fixed and the other way round: sort first, then limit, or the query fails.
Functions
Work with text, round numbers, divide correctly and fill gaps.
- SQL has functions for text: UPPER('oslo') gives OSLO, LENGTH('Oslo') gives 4, and SUBSTR('Oslo', 1, 2) gives Os — positions in SQL start at 1, not 0.
- ROUND(price, 2) rounds to two decimal places and ROUND(price) to a whole number. Integer division is a trap: in SQLite 7 / 2 gives 3, while 7 / 2.0 gives 3.5.
- COALESCE(phone, 'none') returns the first value that is not NULL. It fills gaps in the result without changing the table: missing phones show as none.
The mistake you are about to make
SELECT done / total * 100 FROM tasks;
SELECT done * 100.0 / total FROM tasks;
You work out a percentage the way a calculator does. In SQLite an integer divided by an integer stays an integer: 3 / 4 is 0, and every percentage comes out as zero.
CASE
Turn conditions into values right inside a query.
- CASE turns conditions into values: CASE WHEN price < 5 THEN 'cheap' ELSE 'pricey' END. It is SQL's if and else, and it can stand anywhere a column can.
- Branches are checked top to bottom and the first true WHEN wins. Without ELSE, a row that matches no WHEN gets NULL, which is easy to miss in a long result.
- Because the first match wins, ranges are written from one end: WHEN age < 13 THEN 'child' WHEN age < 20 THEN 'teen' ELSE 'adult'. The second WHEN never sees ages under 13.
The mistake you are about to make
CASE WHEN age < 20 THEN 'teen' WHEN age < 13 THEN 'child' ELSE 'adult' END
CASE WHEN age < 13 THEN 'child' WHEN age < 20 THEN 'teen' ELSE 'adult' END
The branches look like independent checks whose order does not matter. The first match takes the row: an eight-year-old passes age < 20 and becomes teen, never reaching child.