Sorting with ORDER BY

Without instructions, a database returns rows in whatever order is convenient for it — and that order can change. ORDER BY sorts the result by one or more columns or expressions, ascending or descending.

Sorting by one column

SELECT name, price
FROM products
ORDER BY price;

The default direction is ascending (ASC): smallest numbers, earliest dates and A–Z text first.

Descending order

SELECT first_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

DESC sorts largest first. (LIMIT, covered next lesson, keeps just the top five.)

Sorting by several columns

SELECT category, name, price
FROM products
ORDER BY category, price DESC;

Rows are sorted by the first column; the second only breaks ties within it. Each column has its own direction — here categories go A–Z, and within each category the most expensive product comes first.

Sorting by expressions and aliases

SELECT name, ROUND(price * stock, 2) AS stock_value
FROM products
ORDER BY stock_value DESC
LIMIT 4;

Unlike WHERE, ORDER BY runs after the select list is built, so it can use column aliases.

Sorting by column position

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

ORDER BY 2 means "the second column in the select list". It's quick for ad-hoc queries but fragile in saved code: change the select list and the sort silently changes.

Where do NULLs go?

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

Databases disagree: SQLite, MySQL and SQL Server put NULL first in ascending order; PostgreSQL and Oracle put it last. The standard NULLS FIRST / NULLS LAST makes it explicit and is supported by SQLite, PostgreSQL and Oracle:

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

Stable results need a unique sort

SELECT first_name, salary
FROM employees
WHERE job_title = 'Developer'
ORDER BY salary, id;

Amara and Tomás earn the same, so sorting by salary alone leaves their order undefined. Adding a unique column such as id as a final tie-breaker guarantees the same order every time — essential for pagination.

Sorting text: collation

How text sorts — uppercase vs lowercase, accented letters, language-specific rules — is decided by the column's collation. SQLite's default compares raw character codes, so 'Z' sorts before 'a'; COLLATE NOCASE ignores case. Other databases offer language-aware collations.

Try it yourself

List orders newest first, and for orders on the same date, by id.