MySQL IN Operator result set order

Question

In MySQL while using IN operator can we ask MySQL to return result-set(record-set) in same order as the IN set was?

explanation: Suppose we have a table items (item_id, item_name);

and the query:

select * from items where item_id in (1,3,5,7,2,4,6,8);

can we have the result-set(record-set) where the records are in same order as IN operator set. i.e 1,3,5,7,2,4,6,8 of record_ids

This isn't the case; MySQL seems to optimise out the search and gives the default order(same as order of those records stored on File-system).

duplicate of : http://stackoverflow.com/questions/396748/ordering-by-the-order-of-values-in-a-sql-in-clause — Punit, Nov 18 '11 at 06:34

score 11 · Accepted Answer · answered Nov 18 '11 at 06:26

You could use MySQL's field function in your ORDER BY clause:

select *
from items
where item_id in (1,3,5,7,2,4,6,8)
order by field(item_id, 1,3,5,7,2,4,6,8)

The field function:

FIELD(str,str1,str2,str3,...)

Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.

If all arguments to FIELD() are strings, all arguments are compared as strings. If all arguments are numbers, they are compared as numbers. Otherwise, the arguments are compared as double.

MySQL IN Operator result set order

1 Answers1