Re: How to structure a random query
You could also do something like: select field1, field2 from table order by rand() limit 1; HTH Ben - Original Message - From: "Philip Mak" <[EMAIL PROTECTED]> To: "Alec Smith" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, April 18, 2001 11:50 PM Subject: Re: How to structure a random query > On Wed, 18 Apr 2001, Alec Smith wrote: > > > I've got a table of X rows, each with a unique ID as determined by > > auto_increment when the row is inserted into the database. How would I go > > about doing a SELECT on a row of the database and have MySQL return a row > > at random? > > Maybe you can do something with the MySQL RAND() function, which returns a > random number between 0.0 and 1.0 (so you'd have to normalize it to > between 1 and MAX(ID)). > > You may not be able to use the grouping function that way, though. MySQL > doesn't seem to let you use grouping functions in the WHERE clause. > > mysql> select num from ffml where num=floor(rand()*max(num))+1; > ERROR : Invalid use of group function > > Anyone else have an idea? > > -Philip Mak ([EMAIL PROTECTED]) > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to structure a random query
On Wed, 18 Apr 2001, Alec Smith wrote: > I've got a table of X rows, each with a unique ID as determined by > auto_increment when the row is inserted into the database. How would I go > about doing a SELECT on a row of the database and have MySQL return a row > at random? Maybe you can do something with the MySQL RAND() function, which returns a random number between 0.0 and 1.0 (so you'd have to normalize it to between 1 and MAX(ID)). You may not be able to use the grouping function that way, though. MySQL doesn't seem to let you use grouping functions in the WHERE clause. mysql> select num from ffml where num=floor(rand()*max(num))+1; ERROR : Invalid use of group function Anyone else have an idea? -Philip Mak ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How to structure a random query
http://www.mysql.com/doc/M/a/Mathematical_functions.html describes how to do this using the RAND() function. -Original Message- From: Alec Smith [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 18, 2001 6:27 PM To: [EMAIL PROTECTED] Subject: How to structure a random query I'm just starting to learn SQL and MySQL, and am curious as to how the following query might be built. I've got a table of X rows, each with a unique ID as determined by auto_increment when the row is inserted into the database. How would I go about doing a SELECT on a row of the database and have MySQL return a row at random? I figure there must be an easier way then doing a SELECT count(*) from tablename, using that in a random generator in Perl/PHP, and then looking up the resulting row with a second MySQL query. Thanks, Alec - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
