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?

You would have to specify a `ORDER BY` with nested `IF` statements, or a calculated field that sets 5 = 1, 4 = 2, 3 = 3..... and order by that — Pekka, Feb 23 '11 at 12:02
`FIELD()` is much easier to use. No idea why somebody would like to hurt himself by using `IF`'s or a `CASE` — Frank Heikens, Feb 23 '11 at 12:10

score 82 · Accepted Answer · answered Feb 23 '11 at 12:07

82

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

answered Feb 23 '11 at 12:07

Frank Heikens

score 26 · Answer 2 · answered Feb 23 '11 at 12:06

26

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

answered Feb 23 '11 at 12:06

delphist

Ciprian Mocanu · Answer 3 · 2015-10-22T09:50:44.577

2

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

edited Oct 22 '15 at 09:50

answered Feb 23 '11 at 12:10

Ciprian Mocanu

score -1 · Answer 4 · answered Feb 23 '11 at 12:03

-1

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

answered Feb 23 '11 at 12:03

Robbie Tapping

4 Answers4