MySQL - SELECT ... WHERE id IN (..) - correct order

Question

I have the following query

SELECT * FROM table WHERE id IN (5,4,3,1,6)

and I want to retrieve the elements in the order specified in the id in.., meaning it should return:

5 ....
4 ....
3 ....
1 ....
6 ....

Any ideas on how to do that?

Answer 1

Use FIELD():

SELECT * FROM table WHERE id IN (5,4,3,1,6) ORDER BY FIELD(id, 5,4,3,1,6);

Answer 2

Well your going to have to create a Id for each of the id's so:

id | otherid

1 = 5 2 = 4 3 = 3 4 = 1 6 = 6

using the IN STATEMENT only looks to see if those values are in the List, doesnt order them in any specific order

Answer 3

SELECT * FROM table WHERE id IN (5,4,3,1,6) ORDER BY FIELD (id, 5,4,3,1,6)

Answer 4

In case anyone is still searching I just found it..

SELECT * FROM `table` WHERE `id` IN (4, 3, 1) ORDER BY FIELD(`id`, 4, 3, 1)

And a reference for the function you can find HERE