MySQL - ordering by something not ASC or DESC

Question

Say I have a table of flowers with associated colors. Is it possible to get a list of flowers and order them by, say, Yellow first, then Blue, then Red. Basically, I want to specify a list of values and order the result by those values. Possible?

Answer 1

You could use FIND_IN_SET:

ORDER BY FIND_IN_SET(column, 'Yellow,Blue,Red')

..or the ANSI CASE statement:

ORDER BY CASE column
           WHEN 'Yellow' THEN 1
           WHEN 'Blue' THEN 2
           WHEN 'Red' THEN 3
         END

Answer 2

You can add a field in colors table for sort_order and and provide values according to the sort requirement. In the query you can use ORDER BY sort_order ASC

I am expecting that you want to order it according to a dynamic requirement not explicitly for these three colors mentioned. This will allow you complete flexibility to manage the sort order.

Answer 3

My choice would be using the FIELD(str, str1, str2, ...) function.

Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.

So you could use the return value to order your list of results:

ORDER BY FIELD (color, 'Yellow', 'Blue', 'Red')

If you want to reverse the order, just add DESC.