[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` )
ORDER BY id LIMIT 1;
--
Matt Warden
Miami University
Oxford, OH, USA
http://mattwarden.com
This email proudly and graciously contributes to entropy.
More information about the thelist
mailing list