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

[Max Schwanekamp]

mattias at thorslund.us wrote:
> Quoting Matt Warden:
>>SELECT * FROM `table`
>>WHERE id >= (
>>        SELECT MIN(id) +  ROUND( ( MAX(id)-MIN(id) ) * RAND() )
>>        FROM `table` )
>>ORDER BY id LIMIT 1;
> 
> That's pretty elegant. I should have mentioned subqueries too, since it's
> something I work with also.

Yes, it is.  I shoulda thought about subqueries as well... Thanks Matt! 
  Plus it's easy to switch out to other RDBMSs with minimal 
modification.  E.g. in MSSQL, use TOP and add the mandatory second 
argument to ROUND, and remove the backticks or replace em with brackets:
SELECT TOP 1 * FROM table
WHERE id >= (
         SELECT MIN(id) +  ROUND( ( MAX(id)-MIN(id) ) * RAND(),0 )
         FROM table )
ORDER BY id

-- 
Max Schwanekamp
http://www.neptunewebworks.com/