MySQL query "in (a, b, c)", how to return elements in this same order (a, b then c)?

Question

Searching a table for precise elements by their ID in a precise order, how can I use this kind of request:

select * from TABLE where ID in (1, 7, 2)

To sort elements in the order I requested them (ID = 1 then ID = 7 then ID = 2) ? Just as the following :

TABLE
ID | field1 | ...
1  | value  | ...
7  | value  | ...
2  | value  | ...

Given of course that the requested order isn't a natural order of one of the fields (can't do order by field to get the result).

Try `ORDER BY CASE ID WHEN 1 THEN 1 WHEN 7 THEN 2 WHEN 2 THEN 3 END` — Giorgos Betsos, Jun 01 '18 at 13:24

Devsi Odedra · Answer 1 · 2018-06-01T13:35:59.243

1

use FIELDS as below

select * from TABLE where ID in (1, 7, 2) ORDER BY FIELD(ID, 1, 7, 2)

edited Jun 01 '18 at 13:35

answered Jun 01 '18 at 13:26

Devsi Odedra

1 Answers1