mindixx
mindixx

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.

Back to the map