Sorting with ORDER BY

Here’s something that surprises beginners: without instructions, a database doesn’t promise any row order. If order matters, you have to ask for it with ORDER BY.

To list products from cheapest to most expensive:

SELECT name, price
FROM products
ORDER BY price;

Ascending is the default. Add DESC to reverse it:

SELECT name, price
FROM products
ORDER BY price DESC;

You can sort by more than one column. This groups customers by city, then sorts names alphabetically within each city:

SELECT name, city
FROM customers
ORDER BY city, name;

Numbers sort by value, dates by time and text alphabetically. Be careful with numbers stored as text, because ‘10’ sorts before ‘9’.

To keep only the first few rows, standard SQL uses FETCH FIRST 5 ROWS ONLY. MySQL and SQLite use LIMIT 5 instead.

Go deeper: SQLite SELECT reference