ORDER BY in SQLite: ordinare con ASC, DESC e più colonne

Senza ORDER BY l'ordine delle righe non è definito

Una SELECT senza ORDER BY restituisce le righe nell'ordine che fa più comodo a SQLite. Su una tabella piccola spesso sembra l'ordine di inserimento, e questo porta a fidarsene. Non farlo. Appena viene usato un indice, la tabella cresce o il piano della query cambia, quell'ordine può spostarsi senza preavviso.

Se ti interessa l'ordine delle righe, dichiaralo in modo esplicito:

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 ordina in modo crescente per impostazione predefinita. Il risultato arriva come Ada, Boris, Chen, Rosa: in ordine alfabetico, ogni volta, indipendentemente da come la tabella è salvata su disco.

ASC e DESC

ASC significa crescente (dal più piccolo al più grande, dalla A alla Z, dal più vecchio al più recente). DESC significa decrescente, cioè il contrario. ASC è il valore predefinito, quindi lo vedrai quasi sempre omesso:

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;

Così ottieni prima le iscrizioni più recenti. Le date salvate come stringhe ISO 8601 (YYYY-MM-DD) si ordinano correttamente come testo: è uno dei motivi per cui questo formato è preferito per le colonne di date in SQLite, che non ha un tipo data dedicato.

Ordinare per più colonne

Quando la prima colonna di ordinamento ha dei pareggi, SQLite usa la seconda per risolverli. Elenca le colonne separate da virgole, in ordine di 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;

Le righe vengono raggruppate prima per paese (FR prima di US) e, all'interno di ogni paese, ordinate per nome. Ogni colonna può avere la sua direzione:

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;

Paese in ordine crescente, poi dal più recente al più vecchio all'interno di ogni paese. ASC e DESC valgono per la colonna accanto a cui si trovano: non si estendono alle altre.

Ordinare per espressioni e alias

ORDER BY accetta qualsiasi espressione, non solo nomi di colonna. È utile per i valori calcolati:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL,
    quantity INTEGER
);

INSERT INTO products (name, price, quantity) VALUES
    ('Quaderno', 4.50, 30),
    ('Penna',    1.20, 100),
    ('Lampada', 25.00, 5);

SELECT name, price * quantity AS revenue
FROM products
ORDER BY revenue DESC;

L'alias revenue della lista SELECT si può usare tranquillamente in ORDER BY. Puoi anche riscrivere l'espressione, ORDER BY price * quantity DESC, e funziona allo stesso modo.

Puoi ordinare anche per posizione della colonna, anche se è un'abitudine da evitare:

SELECT name, price FROM products ORDER BY 2 DESC;

2 indica la seconda colonna della lista di selezione. Funziona, ma se qualcuno in seguito riordina le colonne, l'ordinamento cambia significato senza che nessuno se ne accorga. Ordina invece per nome o per alias.

Dove finiscono i NULL

NULL significa "sconosciuto", e SQLite deve decidere dove collocare i valori sconosciuti in un ordinamento. La regola predefinita: i NULL vengono per primi con ASC, per ultimi con 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 e Chen compaiono in cima, prima di qualsiasi data reale. Raramente è quello che vuoi per "prima i più recenti". Cambialo con 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;

Ora le date reali vengono per prime e i NULL finiscono in fondo. NULLS FIRST fa il contrario. Entrambi fanno parte dello standard SQL e funzionano in SQLite 3.30 e versioni successive.

Ordinare senza distinguere le maiuscole con COLLATE NOCASE

Il confronto di testo predefinito di SQLite è binario: ordina per code point Unicode. Questo significa che le lettere maiuscole vengono prima delle minuscole, quindi 'Zoe' viene prima di 'apple':

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;

Il risultato è Boris, Zoe, ada, apple: prima le maiuscole, poi le minuscole. Per ordinare senza distinguere maiuscole e minuscole, aggiungi 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;

Ora ottieni ada, apple, Boris, Zoe. NOCASE considera equivalenti solo le lettere ASCII dalla A alla Z e dalla a alla z: non normalizza gli accenti né le lettere non ASCII. Per un ordinamento davvero internazionale ti servirebbe una collation a livello di applicazione, ma per il caso comune dell'inglese NOCASE va benissimo.

Ordine casuale

A volte vuoi le righe in ordine casuale: per scegliere un elemento in evidenza del giorno o campionare righe per i test. La funzione random() di SQLite restituisce un intero casuale; ordina in base a quello:

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

INSERT INTO quotes (text) VALUES
    ('Fallo funzionare, fallo bene, fallo veloce.'),
    ('L''ottimizzazione prematura è la radice di tutti i mali.'),
    ('Semplice è meglio di complesso.'),
    ('La leggibilità conta.');

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

Ogni riga riceve un nuovo valore casuale e l'ordinamento le mescola. Va bene per le tabelle piccole. Sulle tabelle grandi ORDER BY random() è lento: deve calcolare un valore casuale per ogni riga e ordinare l'intero risultato. Per estrarre una sola riga da una tabella enorme ci sono approcci più furbi e più veloci (per esempio scegliere un rowid a caso).

Errori comuni

Alcune cose che fregano spesso:

• Dimenticare ORDER BY e dare per scontato l'ordine. Senza di esso l'ordine non è definito. Anche se sembra stabile, non lo è.
• Ordinare numeri salvati come testo. In ordine lessicografico '10' viene prima di '2'. Se una colonna deve essere ordinata come numero, salvala con un'affinità numerica (oppure fai un cast: ORDER BY CAST(value AS INTEGER)).
• Mescolare ASC e DESC tra le colonne. Ogni colonna ha la sua direzione. ORDER BY a, b DESC ordina a in modo crescente e b in modo decrescente, non entrambe in modo decrescente.
• Ordinare un risultato enorme solo per prendere le prime righe. Abbina ORDER BY a LIMIT e metti un indice sulla colonna di ordinamento: è l'argomento della prossima pagina.

Prossimo passo: LIMIT e OFFSET

L'ordinamento dice a SQLite come disporre le righe; LIMIT e OFFSET gli dicono quante restituirne e da dove partire. Insieme sono il cuore della paginazione e delle query "top N", che vedrai subito dopo.