Er kunnen momenten zijn waarop een specifieke volgorde is vereist in een SQL query die niet kan worden gedaan met behulp van ASC of DESC of met behulp van een speciaal sorteerveld. MySQL heeft een ORDER BY FIELD functie die kan worden gebruikt om dit te doen.
Voorbeeldgegevens
De voorbeeldgegevens in dit bericht gebruiken mijn voorbeeld fruit tabel. Dit is een ietwat eenvoudige tabel, maar hij kan worden gebruikt om het punt in deze post vrij goed te illustreren.
Ordenen op specifieke veldwaarden
De fruittabel heeft een veld “naam” met de volgende unieke waarden: Appel, Banaan, Sinaasappel, Peer. Elk van deze unieke waarden heeft een set variëteiten.
Laten we zeggen, voor het argument, dat we de gegevens in een specifieke volgorde willen ordenen op Banaan, Appel, Peer, Sinaasappel en vervolgens op hun variëteiten. Het is niet mogelijk om dit te doen met een gewone ORDER BY clausule omdat een oplopende of aflopende sortering op dit veld niet zal werken. We zouden ofwel een vorm van sorteerkolom nodig hebben of een ander alternatief.
Met behulp van de FIELD( ) functie in de ORDER BY clausule kunnen we dit bereiken. Het werkt door het specificeren van de kolom te sorteren op en vervolgens de waarden te sorteren in volgorde. Bijvoorbeeld:
SELECT * FROM fruit ORDER BY FIELD(name, 'Banana', 'Apple', 'Pear', 'Orange'), variety;
De resulterende gegevens van de voorbeeld tabel zien er als volgt uit:
+----------+--------+---------------------+| 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 |+----------+--------+---------------------+
De gotcha
Er is een kleine “gotcha” bij het gebruik van deze functie. Alle waarden in de kolom die niet in de FIELD() functie staan, zullen in een min of meer willekeurige volgorde vóór de opgegeven waarden verschijnen. Als u bijvoorbeeld alleen Apple en Banana opgeeft:
SELECT * FROM fruit ORDER BY FIELD(name, 'Banana', 'Apple') DESC, variety;
Dit resulteert in:
+----------+--------+---------------------+| 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 |+----------+--------+---------------------+
Een oplossing voor de gotcha
Hoewel u deze functie normaal alleen zou gebruiken als de exacte kolommen bekend zijn, is een oplossing om de volgorde van de opgegeven velden om te keren en ze in een aflopende volgorde te zetten, en dan een tweede sortering op hetzelfde veld uit te voeren.
Het volgende voorbeeld sorteert, ondanks hoe het eruit ziet, in feite op volgorde van Banana, dan Apple, en dan de andere namen in oplopende volgorde:
SELECT * FROM fruit ORDER BY FIELD(name, 'Apple', 'Banana') DESC, name, variety;
Dit resulteert in:
+----------+--------+---------------------+| 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 |+----------+--------+---------------------+
Dit kan een nuttige oplossing zijn als een specifieke set rijen vóór de andere in de resultatenet moet verschijnen, maar deze zou normaal gesproken niet als eerste verschijnen bij gebruik van een ASC of DESC sorteervolgorde.