Mysql order by specific ID values

Question

You can use ORDER BY and FIELD function. See http://lists.mysql.com/mysql/209784

SELECT * FROM table ORDER BY FIELD(ID,1,5,4,3)

It uses Field() function, Which "Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found" according to the documentation. So actually you sort the result set by the return value of this function which is the index of the field value in the given set.

You should be able to use CASE for this:

ORDER BY CASE id
  WHEN 1 THEN 1
  WHEN 5 THEN 2
  WHEN 4 THEN 3
  WHEN 3 THEN 4
  ELSE 5
END

On the official documentation for mysql about ORDER BY, someone has posted that you can use FIELD for this matter, like this:

SELECT * FROM table ORDER BY FIELD(id,1,5,4,3)

This is untested code that in theory should work.

SELECT * FROM table ORDER BY id='8' DESC, id='5' DESC, id='4' DESC, id='3' DESC

If I had 10 registries for example, this way the ID 1, 5, 4 and 3 will appears first, the others registries will appears next.

Normal exibition 1 2 3 4 5 6 7 8 9 10

With this way

8 5 4 3 1 2 6 7 9 10

There's another way to solve this. Add a separate table, something like this:

CREATE TABLE `new_order` (
  `my_order` BIGINT(20) UNSIGNED NOT NULL,
  `my_number` BIGINT(20) NOT NULL,
  PRIMARY KEY (`my_order`),
  UNIQUE KEY `my_number` (`my_number`)
) ENGINE=INNODB;

This table will now be used to define your own order mechanism.

Add your values in there:

my_order | my_number
---------+----------
       1 |         1
       2 |         5
       3 |         4
       4 |         3

...and then modify your SQL statement while joining this new table.

SELECT *
FROM your_table AS T1
INNER JOIN new_order AS T2 on T1.id = T2.my_number
WHERE ....whatever...
ORDER BY T2.my_order; 

This solution is slightly more complex than other solutions, but using this you don't have to change your SELECT-statement whenever your order criteriums change - just change the data in the order table.