Returning query results in predefined order

Could you include a case expression that maps your IDs 7,2,5,... to the ordinals 1,2,3,... and then order by that expression?


I didn't think this was possible, but found a blog entry here that seems to do the type of thing you're after:

SELECT id FROM table WHERE id in (7,2,5,9,8) 
ORDER BY FIND_IN_SET(id,"7,2,5,9,8");

will give different results to

SELECT id FROM table WHERE id in (7,2,5,9,8) 
ORDER BY FIND_IN_SET(id,"8,2,5,9,7");

FIND_IN_SET returns the position of id in the second argument given to it, so for the first case above, id of 7 is at position 1 in the set, 2 at 2 and so on - mysql internally works out something like

id | FIND_IN_SET
---|-----------
7  | 1
2  | 2
5  | 3

then orders by the results of FIND_IN_SET.


Your best bet is:

ORDER BY FIELD(ID,7,2,4,5,8) 

...but it's still ugly.