SQL order by personal order 1


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 ?

Fab
Latest posts by Fab (see all)

About Fab

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

Leave a comment

Your email address will not be published. Required fields are marked *

One thought on “SQL order by personal order