MySQL sort by some list

Question

I have a list of numbers: 7,1,3,2,123,55 (which are the ids of existing records)

I have a mysql table with the colums id and name, where id is an integer primary key. I want to select records from this table, but in a specific order, for example 7,1,3,2,123,55.

• Is it possible to do this in MyISAM within query, without any post processing?
• What is the simplest way to do this?

Answer 1

Since 1 < 3 < 77 < 123, a simple ORDER BY id would suffice.

If, however, you want to order this way: 77, 3, 123, 1, then you could use function FIELD():

SELECT id, name FROM mytable  WHERE id IN (77, 3, 123, 1)  ORDER BY FIELD(id, 77, 3, 123, 1) 

If your query matches more rows than you list in FIELD

FIELD returns 0 when a row does not match any of the ids you list, i.e. a number smaller than the numbers returned for listed ids. This means, if your query matches more rows than the ones you list, those rows will appear first. For example:

SELECT id, name FROM mytable  WHERE id IN (77, 3, 123, 1, 400)  ORDER BY FIELD(id, 77, 3, 123, 1) 

In this example, the row with ID 400 will appear first. If you want those rows to appear last, simply reverse the list of IDs and add DESC:

SELECT id, name FROM mytable  WHERE id IN (77, 3, 123, 1, 400)  ORDER BY FIELD(id, 1, 123, 3, 77) DESC