MySQL Select WHERE IN given order

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)

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');

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.

Tags:

Mysql