publikz.com publikz.com - 7 months ago 18
PHP Question

MySQL sort by some list

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

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)