Sort the rows according to the order specified in WHERE IN clause

I have something like:

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

It returns the rows ordered by ID, ascending. Is there a way to get back the row开发者_如何学JAVAs in the order specified in the IN clause?

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.

Set based approach: create a table-like structure that contains the id values and sort order, and join:

SELECT tbl.*
FROM (VALUES
    ROW(118, 1),
    ROW(17,  2),
    ROW(113, 3),
    ROW(23,  4),
    ROW(72,  5)
) AS tvc(id, sort)
JOIN tbl ON tvc.id = tbl.id
ORDER BY tvc.sort

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

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