MySQL - ORDER BY values within IN()

Question

Hey -- I'm hoping to sort the items returned in the following query by the order they're entered into the IN() function.

INPUT:

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

OUTPUT:

|   id   |   name  |
^--------^---------^
|   5    |   B     |
|   6    |   B     |
|   1    |   D     |
|   15   |   E     |
|   17   |   E     |
|   9    |   C     |
|   18   |   C     |

Any ideas? Thanks in advance!

Answer 1

You need another column (numeric) in your table, in which you specify the sort order. The IN clause doesn't work this way.

B - 1
A - 2
D - 3
E - 4
C - 5

Answer 2

Try something like

... ORDER BY (CASE NAME WHEN 'B' THEN 0 WHEN 'A' THEN 1 WHEN ...

Answer 3

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.

Answer 4

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.