La clausola ORDER BY in MySQL parte 2

La seconda puntata relativa all'ordinamento dei risulati di una query in MySQL con la clausola ORDER BY

Nell'articolo "La clausola ORDER BY in MySQL parte 1", si è trattato ORDER BY più in generale, in quest'articolo si vuole approfondire le varie tipologie di ordinamento, anche quest'articolo proviene dal blog di uno dei moderatori/editori di MySQL italia: "Sante Caserio"

Tipi di ordinamento

Come si sa, esistono almeno due tipi di ordinamento fondamentali: l’ordinamento alfabetico e l’ordinamento numerico. Naturalmente delle stringhe contenente solo cifre possono essere ordinate numericamente, così come dei numeri possono essere ordinati alfabeticamente.
Qual è la differenza? Se si desidera ordinare i numeri 1000 e 2, numericamente l’ordine sarà: 2, 1000, perchè 2 è ovviamente minore di 1000; mentre l’ordinamento alfabetico sarà: 1000, 2, perchè il carattere ‘1′ viene prima del carattere ‘2′.

Ordine alfabetico

Ci sono casi in cui si desidera ordinare alfabeticamente dei numeri. Il motivo più comune (forse l’unico?) è che i numeri formino un codice, che è considerato una stringa. Allora le query SQL dovranno trasformare il dato numerico in stringa e MySQL ci permette di farlo nel modo seguente:

SELECT numero FROM t ORDER BY CONCAT(numero, '')

La funzione CONCAT concatena tra loro più stringhe e restituisce il risultato, che è a sua volta una stringa. Concatenando un qualsiasi dato con una stringa vuota non lo si altera, ma il risultato è una stringa. Pertanto, nell’esempio, numero sarà ordinato alfabeticamente.
Un altro esempio può essere:

SELECT CONCAT(int1, int2) AS i FROM t ORDER BY i

Si tenga comunque presente che, se uno dei parametri passati a CONCAT è NULL, il risultato sarà anch’esso NULL. I valori NULL vengono sempre per primi.

Ordine numerico

E’ possibile ordinare numericamente delle stringhe. Il metodo per farlo si basa sullo stesso concetto appena esposto, cioè:

SELECT codice + 0 AS c FROM t ORDER BY c

Aggiungendo 0 a codice non si modifica il suo valore, ma se era una stringa il risultato c sarà necessariamente un numero, in quanto il risultato di un’addizione non può essere altro che un numero.
Questo è utile anche nel caso in cui alcuni numeri vengano concatenati tra loro come se fossero delle stringhe per ottenere un codice:

SELECT CONCAT(int1, int2, int3) + 0 AS codice ORDER BY codice

Lo stesso risultato si può ottenere agendo sui valori booleani, che corrispondono numericamente a 0 (falso) e 1 (vero):

SELECT CONCAT(bool1, bool2, int1) + 0 AS codice ORDER BY codice

Ordinamento booleano

Questo è il tipo di ordinamento che offre i risvolti più interessanti. Non si tratta per forza di ordinare campi booleani: l’ordinamento può essere effettuato su una qualsiasi espressione. Di conseguenza, se si desidera che i primi record siano quelli in cui i campi a e b sono uguali, si può usare una delle seguenti query:

SELECT * FROM x  ORDER BY (a=b) DESC
SELECT * FROM x  ORDER BY (a<>b)

Bisogna infatti ricordare che il valore false (0) viene prima di true (1). Questo aspetto in questi casi può trarre in inganno.

Non è il caso di dilungarsi qui sulle infinite possibilità dell’ordinamento booleano, ma ecco una rapida rassegna di esempi:

• Prima i non-NULL (l’equivalente del NULLS LAST di Oracle): ORDER BY (int IS NOT NULL)
  
• Prima i numeri pari: ORDER BY ((int1 MOD 2)>0)
  
• ecc ecc
• Prima le stringhe non vuote: ORDER BY LENGTH(i1)=0
  
• Oppure, più ottimizzato: ORDER BY (int1 & 1)

In generale, le espressioni che si possono utilizzare nella WHERE, che sono booleane, si possono utilizzare anche nella ORDER BY.
Ordinamento casuale

Se non si è già letto il precedente articolo sull’ordinamento casuale si consiglia di leggerlo. Qui si intende solo aggiungere che in alcuni casi è possibile ottimizzarlo. Ordinare in base alla funzione rnd(), infatti, è efficiente sulle tabelle di piccole dimensioni, cioè aventi poche righe. Ma con le tabelle aventi molte righe, il costo di questa operazione cresce esponenzialmente con il numero delle righe.

Se ad ogni query abbiamo bisogno che l’ordine sia differente, non c’è niente da fare: dobbiamo usare rand(). Sarebbe inutile, inoltre, prelevare un grande numero di righe con una query, farla passare attraverso una rete (anche locale) e poi ordinarle da programma. Oltre ai ritardi dovuti all’estrazione e alla trasmissione di un grosso numero di righe, si creerebbe traffico inutile.

C’è però qualche caso in cui si vuole semplicemente che le righe siano “sparpagliate”, non che ad ogni estrazione seguano un ordine differente. Allora si può semplicemente aggiungere un campo alla tabella, avente un valore casuale:

ALTER TABLE t ADD COLUMN r INTEGER UNSIGNED DEFAULT NULL

Tutto sarebbe più semplice se MySQL, come la maggior parte dei suoi concorrenti, permettesse di assegnare come valore di default un’espressione SQL. Chi utilizza DBMS che consentono questo, come PostgreSQL e Firebird, può usare la seguente query:

ALTER TABLE t ADD COLUMN r INTEGER UNSIGNED DEFAULT rand()

Chi usa MySQL, invece, dovrà assegnare un valore casuale manualmente:

UPDATE t SET r=rand()

Quando nuovi record vengono inseriti, occorre inserire esplicitamente il valore casuale in r. Se questo non è possibile (perchè ci sono già molti programmi che utilizzano quel db e noi non possiamo o non vogliamo modificarli tutti) si può assegnare il valore casuale ai nuovi record periodicamente:

UPDATE t SET r=rand() WHERE r IS NUL

Inoltre, se di tanto in tanto si desidera modificare l’ordinamento, è sufficiente eseguire periodicamente la prima query (è l’unica operazione di manutenzione dei valori casuali necessaria per gli utenti di PostgreSQL, Firebird, ecc):

UPDATE t SET r=rand()

Argomenti chiave:

• order by
• sql