[thelist] MySQL select random row [WAS: MySQL queries and AUTO_INCREMENT]

Matt Warden mwarden at gmail.com
Fri Apr 7 14:41:39 CDT 2006

On 4/7/06, Max Schwanekamp <lists at neptunewebworks.com> wrote:

> OK, so I googled around a bit just now, and these guys have a good
> discussion:
> http://akinas.com/pages/en/blog/mysql_random_row/
> The last one is an interesting solution.

It's the same thing you suggested, just using SQL subqueries (which I
assumed weren't available):

SELECT * FROM `table` WHERE id >= (SELECT FLOOR( MAX(id) * RAND())
FROM `table` ) ORDER BY id LIMIT 1;

Except it's not quite right. It should be:

SELECT * FROM `table`
WHERE id >= (
         SELECT MIN(id) +  ROUND( ( MAX(id)-MIN(id) ) * RAND() )
         FROM `table` )

Matt Warden
Miami University
Oxford, OH, USA

This email proudly and graciously contributes to entropy.

More information about the thelist mailing list