Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL IN Operator result set order

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).

like image 707
जलजनक Avatar asked Nov 18 '11 06:11

जलजनक


1 Answers

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.

like image 115
mu is too short Avatar answered Sep 30 '22 23:09

mu is too short