SQL ORDER BY: sort the results

Without specific instructions, a database returns rows in whatever order suits it, and that order can change from one run to the next. To get a reliable ranking (cheapest to most expensive, newest to oldest, alphabetical order), you add ORDER BY.

Syntax

SELECT columns
FROM table
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

How it works

• ASC sorts in ascending order (A → Z, 1 → 9, oldest → newest). It is the default, so you can leave it out.
• DESC sorts in descending order.
• You can sort on several columns: the second one is only used to break ties on the first.

ORDER BY goes at the end of the query, after WHERE, GROUP BY and HAVING. Since it runs last (just before LIMIT), it can use the aliases defined in the SELECT.

In SQLite, NULL values are considered the smallest: they come first in ascending order. You can change this with NULLS LAST.

Diagram

Simplified diagram to show the idea. The examples below use the real SQL Quest practice database (a fictional online shop: see the full schema).

Examples with their results

SELECT nom, prix
FROM produits
ORDER BY prix
LIMIT 8;

SELECT id, date_commande, statut
FROM commandes
ORDER BY date_commande DESC
LIMIT 6;

SELECT departement, prenom, nom, salaire
FROM employes
ORDER BY departement, salaire DESC
LIMIT 10;

SELECT nom, prix * stock AS valeur_stock
FROM produits
ORDER BY valeur_stock DESC
LIMIT 5;

Common mistakes

SELECT * FROM employes ORDER BY salaire, departement DESC;

SELECT * FROM employes ORDER BY salaire DESC, departement DESC;

SELECT * FROM produits ORDER BY prix WHERE stock > 0;

SELECT * FROM produits WHERE stock > 0 ORDER BY prix;

SELECT nom FROM produits LIMIT 3;

SELECT nom FROM produits ORDER BY nom LIMIT 3;

Exercises

Try writing the query yourself before opening the solution.

SELECT ville, nom, prenom FROM clients ORDER BY ville, nom;

SELECT nom, stock FROM produits ORDER BY stock DESC;

Frequently asked questions