Random SQL Queries

A year or so ago I was given the task of returning rows of data from a MySQL table in a randomised fashion. This in itself is fairly easy but the project which I was working on displays the data in a paginated format so a constantly shifting random data order would not work. The client also requested that the random order should “re-shuffle” every once in a while.

My solution was to seed the random order with a hash of the current year and month concatenated with each row’s ID value. This means that the random order would be re-shuffled on a monthly basis.

The only caveat of this method is that it’s possible for an end-user of the system to be looking at the site at time of shift, messing up their pagination. But then, with a shifting random order there would always be that possibility!

SELECT *, MD5(CONCAT(DATE_FORMAT(NOW(), '%Y%m'), `id_column`)) AS `rand_col`
FROM `table`
ORDER BY `rand_col` ASC
Share Button