sql - Order items in MySQL by a fixed list?

Question

I am going to have a fixed list of items to order by that I won't know until I run the query since there is a randomization step.

I would like to have something like the following:

Assume that is_launch_set will return 1, 3, 7, 11 but have been randomized to below:

SELECT * FROM items WHERE is_launch_set=1 ORDER BY id values (3,11,7,1);

Any ideas on how to achieve this? I was thinking maybe a find_in_set but not really sure.

question from:https://stackoverflow.com/questions/10940402/order-items-in-mysql-by-a-fixed-list

Answer 1

You can do that by using either:

ORDER BY FIND_IN_SET(id, '3,11,7,1')

or

ORDER BY FIELD(id, 3, 11, 7, 1)

or

ORDER BY CASE id WHEN 3 THEN 0
                WHEN 11 THEN 1
                 WHEN 7 THEN 2
                 WHEN 1 THEN 3
                        ELSE 4
         END