To avoid another heaving PHP/other language sorting process on results from complex SQL queries I wanted to reorder my way, I’ve discovered an interesting property in SQL to order according special values using SQL FIELD (documentation on MySQL FIELD here, and scroll).

Example :

-- Let's I have a table of articles (id, name, color, size) 
-- and I want the red articles first, then green, then yellow
-- and finally blue
SELECT *
FROM articles
ORDER BY FIELD(color, 'red','green','yellow', 'blue');

Then, to be sure that you won’t have any extra results that won’t match your color criteria and therefore be in the first place of your sorted list, you can add a IN on the field you want to sort.

Example :

SELECT *
FROM articles
WHERE color IN ('red','green','yellow', 'blue')
ORDER BY FIELD(color, 'red','green','yellow', 'blue');

Nice huh ?

• About
• Latest Posts

Fab

Solutions Architect at Fearless Dinosaur

Solutions Architect, I build great workflows for the news, media and broadcast industries. I play with data too.

Latest posts by Fab (see all)

• La Commune : review - 30 March 2024
• Fearless Dinosaur - 12 March 2024
• Au Printemps des Monstres : review - 25 November 2023