Sorting with ORDER BY

In this lesson, you'll learn to put the rows of a result in order, to show just some of them, and to calculate new columns. So far, the rows have come in whatever order the database gave them. But a shop usually wants them in a particular order: the cheapest games first, the newest orders at the top, the customers in alphabetical order. That's what ORDER BY is for. It goes at the end of the query, after FROM, followed by the column to sort by:

SELECT title, price
FROM games
ORDER BY price;

The rows are sorted in ascending order, from the smallest value to the largest. What “smallest” means depends on what's in the column:

• Numbers sort by their value: 5, 10, 15, 20.
• Text sorts alphabetically, letter by letter: Emma Clark comes before Grace Hall, and Leo Walker before Liam Turner. SQLite compares the characters by their codes, in which all capital letters come before all lowercase ones. That's why the genre RPG comes before Racing: P is a capital letter, a isn't.
• Dates are stored as text, in the format YYYY-MM-DD: the year, the month and the day, such as 2024-03-14, always with two digits for the month and the day. Text is compared from the left, so the year decides first, then the month, then the day, and the alphabetical order is also the order in time. That's exactly why databases write dates this way: 3/14/2024 wouldn't sort right.
• NULL counts as smaller than any value in SQLite, so the rows with NULL come first. (Some other databases, such as PostgreSQL, put them last.)

Without ORDER BY, the order of the rows isn't guaranteed. Usually, SQLite gives them in the order of their IDs, but not always. Run SELECT email FROM customers; and the emails come in alphabetical order. That's because SQLite also keeps the emails in a separate sorted list, which it uses to check that no two customers have the same email. (It's called an index, and you'll learn about indexes at the end of the course.) When you ask for nothing but the emails, reading that short list is quicker than reading the whole table. Ask for the names too (SELECT name, email FROM customers;), and the rows are back in the order of the IDs. The database always picks the way it thinks is fastest, and that can change with the query, with the data, or with another database. So whenever the order matters, write ORDER BY.

Typical mistake: ORDER BY in the wrong place. The parts of a query come in a fixed order: SELECT, then FROM, and ORDER BY at the end. A query that has them mixed up ends with an error:

SELECT title, price
ORDER BY price
FROM games;

Error on line 1: near "FROM": syntax error