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

[Matt Warden]

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` )
ORDER BY id LIMIT 1;

--
Matt Warden
Miami University
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.