MySQL - ORDER BY values within IN()

Another option from here: http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html

select * 
from tablename 
order by priority='High' DESC, priority='Medium' DESC, priority='Low" DESC;

So in your case (untested) would be

SELECT id, name
FROM mytable
WHERE name IN ('B', 'A', 'D', 'E', 'C')
ORDER BY name = 'B', name = 'A', name = 'D', name =  'E', name = 'C';

Depending what you're doing I've found it a bit quirky but always got it to work after playing with it a bit.

SELECT id, name
FROM mytable
WHERE name IN ('B', 'A', 'D', 'E', 'C')
ORDER BY FIELD(name, 'B', 'A', 'D', 'E', 'C')

The FIELD function returns the position of the first string in the remaining list of strings.

However, it is much better performance-wise to have an indexed column that represents your sort order, and then sort by this column.

Related