Maintaining order in MySQL "IN" query

Question

I have the following table

DROP TABLE IF EXISTS `test`.`foo`; CREATE TABLE  `test`.`foo` (   `id` int(10) unsigned NOT NULL auto_increment,   `name` varchar(45) NOT NULL,   PRIMARY KEY  (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

Then I try to get records based on the primary key

SELECT * FROM foo f where f.id IN (2, 3, 1); 

I then get the following result

+----+--------+ | id | name   | +----+--------+ |  1 | first  | |  2 | second | |  3 | third  | +----+--------+ 3 rows in set (0.00 sec) 

As one can see, the result is ordered by id. What I'm trying to achieve is to get the results ordered in the sequence I'm providing in the query. Given this example it should return

+----+--------+ | id | name   | +----+--------+ |  2 | second | |  3 | third  | |  1 | first  | +----+--------+ 3 rows in set (0.00 sec) 

Answer 1

As the other answer mentions: the query you posted has nothing about what order you'd like your results, just which results you'd like to get.

To order your results, I would use ORDER BY FIELD():

SELECT * FROM foo f where f.id IN (2, 3, 1) ORDER BY FIELD(f.id, 2, 3, 1); 

The argument list to FIELD can be variable length.