Mysql order by specific ID values

Is it possible to sort in mysql by "order by" using predefined set of column values (ID) like: order by (ID=1,5,4,3) so I would get record 1, 5, 4, 3 in that order out?

UPDATE: About abusing mysql ;-) I have to explain why I need this...

I want my records change sort randomly every 5 minutes. I have a cron task to do the update table to put different, random sort order in it. There is just one problem! PAGINATION. I will have a visitor who comes to my page and I give him first 20 results. He will wait 6 minutes and go to page 2 and he will have wrong results as the sort order had allready changed.

So I thought that if he comes to my site I put all the ID's to a session and when he is in page 2 he get's the correct records out even if the sorting allready changed.

Is there any other way, better, to do this?

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.

If you need to order a single id first in the result, use the id.

select id,name 
from products
order by case when id=5 then -1 else id end

If you need to start with a sequence of multiple ids, specify a collection, similar to what you would use with an IN statement.

select id,name 
from products
order by case when id in (30,20,10) then -1 else id end,id

If you want to order a single id last in the result, use the order by the case. (Eg: you want "other" option in last and all city list show in alphabetical order.)

select id,city 
from city
order by case 
when id = 2 then city else -1 
end, city ASC

If i had 5 city for example, i want to show the city in alphabetical order with "other" option display last in the dropdown then we can use this query. see example other are showing in my table at second id(id:2) so i am using "when id = 2" in above query.

record in DB table:

Bangalore - id:1
Other     - id:2
Mumbai    - id:3
Pune      - id:4
Ambala    - id:5

my output:

Ambala  
Bangalore  
Mumbai  
Pune
Other

SELECT * FROM TABLE ORDER BY (columnname,1,2) ASC OR DESC