SQLite ORDER BY : trier ASC, DESC et multi-colonnes

Sans ORDER BY, l'ordre des lignes n'est pas garanti

Un SELECT sans ORDER BY renvoie les lignes dans l'ordre qui arrange SQLite à ce moment-là. Sur une petite table, ça ressemble souvent à l'ordre d'insertion, et c'est exactement ce qui pousse les gens à s'y fier. Mauvaise idée. Dès qu'un index entre en jeu, que la table grossit ou que le plan d'exécution change, cet ordre peut basculer sans crier gare.

Si l'ordre des résultats compte pour vous, il faut le dire explicitement avec ORDER BY :

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT,
    signup_date TEXT
);

INSERT INTO users (name, country, signup_date) VALUES
    ('Boris', 'FR', '2024-03-12'),
    ('Ada',   'US', '2024-01-05'),
    ('Rosa',  'FR', '2024-02-20'),
    ('Chen',  'US', '2024-03-01');

SELECT name, country FROM users ORDER BY name;

ORDER BY name trie par ordre croissant par défaut. Vous obtenez donc Ada, Boris, Chen, Rosa — par ordre alphabétique, à tous les coups, peu importe la façon dont la table est stockée sur le disque.

ASC et DESC

ASC signifie ascendant (du plus petit au plus grand, de A à Z, du plus ancien au plus récent). DESC veut dire descendant, c'est-à-dire l'inverse. Comme ASC est la valeur par défaut, on l'omet quasiment toujours :

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    signup_date TEXT
);

INSERT INTO users (name, signup_date) VALUES
    ('Boris', '2024-03-12'),
    ('Ada',   '2024-01-05'),
    ('Rosa',  '2024-02-20'),
    ('Chen',  '2024-03-01');

SELECT name, signup_date FROM users ORDER BY signup_date DESC;

Vous obtenez ainsi les inscriptions les plus récentes en premier. Les dates stockées au format ISO 8601 (YYYY-MM-DD) se trient correctement en tant que texte — c'est d'ailleurs l'une des raisons pour lesquelles ce format est privilégié pour les colonnes de dates dans SQLite, qui ne dispose pas de type date dédié.

Tri sur plusieurs colonnes en SQLite

Quand la première colonne de tri donne lieu à des ex æquo, SQLite départage avec la deuxième colonne. Listez simplement les colonnes séparées par des virgules, dans l'ordre de priorité :

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

INSERT INTO users (name, country) VALUES
    ('Boris', 'FR'),
    ('Ada',   'US'),
    ('Rosa',  'FR'),
    ('Chen',  'US');

SELECT name, country FROM users ORDER BY country, name;

Les lignes sont d'abord regroupées par pays (FR avant US), puis triées par nom à l'intérieur de chaque pays. Chaque colonne peut avoir son propre sens de tri :

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT,
    signup_date TEXT
);

INSERT INTO users (name, country, signup_date) VALUES
    ('Boris', 'FR', '2024-03-12'),
    ('Ada',   'US', '2024-01-05'),
    ('Rosa',  'FR', '2024-02-20'),
    ('Chen',  'US', '2024-03-01');

SELECT name, country, signup_date
FROM users
ORDER BY country ASC, signup_date DESC;

Pays par ordre croissant, puis du plus récent au plus ancien à l'intérieur de chaque pays. Petit piège classique : ASC et DESC s'appliquent uniquement à la colonne qui les précède, ils ne se propagent pas aux suivantes.

Trier par expression ou par alias

La clause ORDER BY accepte n'importe quelle expression, pas seulement des noms de colonnes. Pratique quand on veut trier sur une valeur calculée :

CREATE TABLE produits (
    id INTEGER PRIMARY KEY,
    nom TEXT,
    prix REAL,
    quantite INTEGER
);

INSERT INTO produits (nom, prix, quantite) VALUES
    ('Cahier', 4.50, 30),
    ('Stylo',  1.20, 100),
    ('Lampe', 25.00, 5);

SELECT nom, prix * quantite AS chiffre_affaires
FROM produits
ORDER BY chiffre_affaires DESC;

L'alias revenue défini dans la clause SELECT est tout à fait utilisable dans le ORDER BY. Vous pouvez aussi réécrire l'expression complète — ORDER BY price * quantity DESC — le résultat sera identique.

Il est également possible de trier par position de colonne, mais c'est une habitude à éviter :

SELECT name, price FROM products ORDER BY 2 DESC;

2 désigne la deuxième colonne de la liste du SELECT. Ça fonctionne, mais si quelqu'un réordonne les colonnes plus tard, le tri change discrètement de sens. Mieux vaut trier par nom de colonne ou par alias.

Où atterrissent les NULL

Un NULL représente une valeur « inconnue », et SQLite doit bien décider où placer ces inconnus dans le tri. La règle par défaut : les NULL arrivent en premier en ASC, et en dernier en DESC.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    signup_date TEXT
);

INSERT INTO users (name, signup_date) VALUES
    ('Boris', '2024-03-12'),
    ('Ada',   NULL),
    ('Rosa',  '2024-02-20'),
    ('Chen',  NULL);

SELECT name, signup_date FROM users ORDER BY signup_date;

Ada et Chen se retrouvent tout en haut, avant la moindre vraie date. Ce n'est presque jamais ce qu'on veut quand on cherche les entrées « les plus récentes d'abord ». La parade : forcer NULLS LAST :

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    signup_date TEXT
);

INSERT INTO users (name, signup_date) VALUES
    ('Boris', '2024-03-12'),
    ('Ada',   NULL),
    ('Rosa',  '2024-02-20'),
    ('Chen',  NULL);

SELECT name, signup_date FROM users
ORDER BY signup_date DESC NULLS LAST;

Du coup, les vraies dates remontent en premier et les NULL se retrouvent tout en bas. NULLS FIRST fait l'inverse. Les deux font partie du SQL standard et fonctionnent à partir de SQLite 3.30.

Tri insensible à la casse avec COLLATE NOCASE

Par défaut, SQLite compare le texte de façon binaire — il trie selon les points de code Unicode. Résultat : les majuscules passent avant les minuscules, donc 'Zoe' arrive avant 'apple' :

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);

INSERT INTO users (name) VALUES ('pomme'), ('Zoé'), ('Boris'), ('ada');

SELECT name FROM users ORDER BY name;

Le résultat donne Boris, Zoe, ada, apple : d'abord les majuscules, puis les minuscules. Pour obtenir un tri insensible à la casse, il suffit d'ajouter la collation NOCASE :

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);

INSERT INTO users (name) VALUES ('apple'), ('Zoe'), ('Boris'), ('ada');

SELECT name FROM users ORDER BY name COLLATE NOCASE;

Vous obtenez maintenant ada, apple, Boris, Zoe. Attention : NOCASE considère uniquement les lettres ASCII A–Z et a–z comme équivalentes — il ne normalise ni les accents, ni les caractères non-ASCII. Pour un vrai tri internationalisé, il faudrait passer par une collation définie côté application, mais NOCASE fait largement l'affaire pour l'anglais courant.

Tri aléatoire avec ORDER BY RANDOM

Parfois, on veut récupérer les lignes dans un ordre aléatoire — pour choisir un élément mis en avant chaque jour, ou pour échantillonner des lignes lors de tests. La fonction random() de SQLite renvoie un entier aléatoire ; il suffit de trier sur cette valeur :

CREATE TABLE quotes (id INTEGER PRIMARY KEY, text TEXT);

INSERT INTO quotes (text) VALUES
    ('Faites que ça marche, faites que ce soit juste, faites que ce soit rapide.'),
    ('L''optimisation prématurée est la racine de tous les maux.'),
    ('Simple est mieux que complexe.'),
    ('La lisibilité compte.');

SELECT text FROM quotes ORDER BY random() LIMIT 1;

Chaque ligne reçoit une nouvelle valeur aléatoire, puis le tri les mélange. C'est très bien sur de petites tables. Sur les grosses tables, en revanche, ORDER BY random() devient lent : SQLite doit calculer une valeur aléatoire pour chaque ligne, puis trier tout le résultat. Pour piocher une seule ligne au hasard dans une énorme table, mieux vaut passer par des approches plus malines (tirer un rowid au hasard, par exemple).

Pièges classiques à éviter

Voici quelques erreurs qui reviennent souvent :

• Oublier ORDER BY en supposant que l'ordre est garanti. Sans clause de tri, l'ordre est indéterminé. Même s'il semble stable, il ne l'est pas.
• Trier des nombres stockés sous forme de texte. En tri lexicographique, '10' arrive avant '2'. Si une colonne doit être triée numériquement, donne-lui une affinité numérique (ou utilise un cast : ORDER BY CAST(value AS INTEGER)).
• Mélanger ASC et DESC sur plusieurs colonnes. Chaque colonne a son propre sens de tri. ORDER BY a, b DESC trie a en ordre croissant et b en ordre décroissant — pas les deux en décroissant.
• Trier un énorme résultat juste pour ne garder que les premières lignes. Combine ORDER BY avec LIMIT, et pose un index sur la colonne de tri — c'est justement le sujet de la section suivante.

La suite : LIMIT et OFFSET

ORDER BY indique à SQLite comment ranger les lignes ; LIMIT et OFFSET lui disent combien en renvoyer et à partir d'où commencer. Ensemble, ils forment la base de la pagination et des requêtes « top N » — on attaque ça tout de suite.