Jerry2 Jerry2 - 2 months ago 7
MySQL Question

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?

Answer

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.