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, I build great workflows for the news and broadcast industries. I play with data too.

Latest posts by Fab (see all)

• The Tent: review - 27 August 2023
• The Old Man and the Sea: review - 8 June 2023
• Ubik: review - 24 March 2023