MySQL WHERE IN - Ordering

Question

When using:

SELECT * FROM some_table WHERE id IN(4,2,1,3,5);

The resulting order is:

1,2,3,4,5

What can I do to return the results in the same order I queried them in?

thanks

possible duplicate of http://stackoverflow.com/questions/396748/ordering-by-the-order-of-values-in-a-sql-in-clause — itsmatt, Apr 09 '10 at 18:50

score 7 · Accepted Answer · answered Apr 09 '10 at 18:54

7

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

More info on MySQL FIELD() function

answered Apr 09 '10 at 18:54

maček

76,434
37
167
198

score 1 · Answer 2 · answered Apr 09 '10 at 18:45

SELECT *
FROM some_table 
WHERE id IN (4,2,1,3,5)
order by case id
    when 4 then 1
    when 2 then 2
    when 1 then 3
    when 3 then 4
    when 5 then 5
end

or

SELECT * FROM some_table WHERE id = 4
union all
SELECT * FROM some_table WHERE id = 2
union all
SELECT * FROM some_table WHERE id = 1
union all
SELECT * FROM some_table WHERE id = 3
union all
SELECT * FROM some_table WHERE id = 5

MySQL WHERE IN - Ordering

2 Answers2