Ci possono essere momenti in cui è richiesto un ordine specifico in una query SQL che non può essere fatto usando ASC o DESC o usando un campo di ordinamento speciale. MySQL ha una funzione ORDER BY FIELD che può essere usata per fare questo.
Dati di esempio
I dati di esempio in questo post usano la mia tabella di esempio sulla frutta. Questa è una tabella un po’ semplice ma può essere usata per illustrare abbastanza bene il punto di questo post.
Ordinamento per valori di campi specifici
La tabella della frutta ha un campo “nome” con i seguenti valori unici: Mela, Banana, Arancia, Pera. Ognuno di questi valori unici ha un insieme di varietà.
Diciamo, per ipotesi, che vogliamo ordinare i dati in un ordine specifico per Banana, Mela, Pera, Arancia e poi per le loro varietà. Non è possibile farlo usando una normale clausola ORDER BY perché un ordinamento ascendente o discendente su questo campo non funzionerà. Avremmo bisogno di qualche forma di colonna di ordinamento o di un’altra alternativa.
Utilizzando la funzione FIELD( ) nella clausola ORDER BY possiamo ottenere questo risultato. Funziona specificando la colonna per cui ordinare e poi i valori da ordinare. Per esempio:
SELECT * FROM fruit ORDER BY FIELD(name, 'Banana', 'Apple', 'Pear', 'Orange'), variety;
I dati risultanti dalla tabella di esempio assomigliano a questo:
+----------+--------+---------------------+| fruit_id | name | variety |+----------+--------+---------------------+| 11 | Banana | Burro || 12 | Banana | Cavendish || 10 | Banana | Plantain || 6 | Apple | Cox's Orange Pippin || 7 | Apple | Granny Smith || 1 | Apple | Red Delicious || 8 | Pear | Anjou || 4 | Pear | Bartlett || 2 | Pear | Comice || 5 | Orange | Blood || 3 | Orange | Navel || 9 | Orange | Valencia |+----------+--------+---------------------+
Il problema
C’è un piccolo problema quando si usa questa funzione. Tutti i valori che sono nella colonna che non sono nella funzione FIELD() appariranno in un ordine più o meno casuale prima dei valori specificati. Per esempio, specificando solo Apple e Banana:
SELECT * FROM fruit ORDER BY FIELD(name, 'Banana', 'Apple') DESC, variety;
Il risultato è:
+----------+--------+---------------------+| fruit_id | name | variety |+----------+--------+---------------------+| 6 | Apple | Cox's Orange Pippin || 7 | Apple | Granny Smith || 1 | Apple | Red Delicious || 11 | Banana | Burro || 12 | Banana | Cavendish || 10 | Banana | Plantain || 8 | Pear | Anjou || 4 | Pear | Bartlett || 5 | Orange | Blood || 2 | Pear | Comice || 3 | Orange | Navel || 9 | Orange | Valencia |+----------+--------+---------------------+
Una soluzione al problema
Anche se normalmente si usa questa funzione solo quando le colonne esatte sono note, una soluzione è invertire l’ordine dei campi specificati e ordinarli in ordine decrescente, e poi fare un secondo ordinamento sullo stesso campo.
L’esempio seguente, nonostante l’aspetto, in realtà ordina in ordine di Banana, poi Apple, e poi gli altri nomi in ordine crescente:
SELECT * FROM fruit ORDER BY FIELD(name, 'Apple', 'Banana') DESC, name, variety;
Il risultato è:
+----------+--------+---------------------+| fruit_id | name | variety |+----------+--------+---------------------+| 11 | Banana | Burro || 12 | Banana | Cavendish || 10 | Banana | Plantain || 6 | Apple | Cox's Orange Pippin || 7 | Apple | Granny Smith || 1 | Apple | Red Delicious || 5 | Orange | Blood || 3 | Orange | Navel || 9 | Orange | Valencia || 8 | Pear | Anjou || 4 | Pear | Bartlett || 2 | Pear | Comice |+----------+--------+---------------------+
Questa può essere una soluzione utile se una specifica serie di righe deve apparire prima delle altre nel set di risultati, ma normalmente non apparirebbe prima quando si usa un ordine ASC o DESC.