MySQL Select WHERE IN given order

Question

I have something like

SELECT * FROM table WHERE id IN (118,17,113,23,72);

If I just do this it returns the rows in ID ascending order. Is there a way to get back the rows in the order given in the IN statement?

Answer 1

Try using FIND_IN_SET:

SELECT * FROM table WHERE id IN (118,17,113,23,72) 
    ORDER BY FIND_IN_SET(id, '118,17,113,23,72');

Answer 2

One option is to use UNION:

SELECT * FROM table WHERE id = 118
UNION
SELECT * FROM table WHERE id = 17
UNION
SELECT * FROM table WHERE id = 113
...

Answer 3

You can create a number to sort on based on the id values:

select *
from table
where id in (118,17,113,23,72)
order by
  case id
    when 118 then 1
    when 17 then 2
    when 133 then 3
    when 23 then 4
    when 72 then 5
  end

Answer 4

this is the first thing that pops to mind. note sql is untested, you might need to check correct syntax

its a bit cumbersome, but might do the trick

select * from table where id = 118
union 
select * from table where id = 17
union 
.... and so on

Answer 5

I think if you did a UNION query with each select, it might return it in the order.

SELECT * FROM table WHERE id=118
UNION
SELECT * FROM table WHERE id=17
...

Ugly, but I think it will work.

Answer 6

You can create a temp table with two columns (ID, order_num):

ID   order_num
118  1
17   2
113  3
23   4
72   5

Then join:

SELECT * from table
INNER JOIN #temp_table 
ON table.id = #temp_table.id

Notice that you can drop the IN clause.

Sometimes I actually create a permanent table, because then when the client inevitably changes their mind about the ordering, I don't have to touch the code, just the table.

Edit

The answer using ORDER BY FIELD() (which I didn't know about) is probably what you want.

Answer 7

You should use "ORDER BY FIELD". So, for instance:

SELECT * FROM table WHERE id IN (118,17,113,23,72) 
ORDER BY FIELD(id,118,17,113,23,72)