MySQL - ORDER BY values within IN()

user_32881 0
Viewed 93278

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?

where-clause sql-order-by mysql
6 Answers
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.

user_400050

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.

user_1103370

Try something like 

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

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
user_1029370

just use 

order by INSTR( ',B,C,D,A,' ,  concat(',' , `field`, ',' ) )

avoid the situation like 

 INSTR('1,2,3,11' ,`field`)

will end with unordered result row : 1 and 11 alternant

user_88069510
Related