Random Selects
Hello- I'm trying to get random information out of my table and the query I'm using keeps returning the same row every time. In the table I have 3 rows and I want to choose at random in the sql which row to return. Here's an example query I have. SELECT * FROM banner ORDER BY rand() asc limit 0,1 This returns the first row in the table every time. What's strange is that I have a query for a table that has about 500 rows in it and it works fine with the exact same syntex (except the the table is different). Is there anything I'm doing wrong? Thanks!
Re: Random Selects
Hi Jay, There seemed to be a problem with the first use of RAND() on new connections (is this in a script?). It was supposed to be fixed in a recent version of MySQL (I think in 3.23.56 and 4.0.10). Anyway, the workaround I discovered to work is to just call RAND() at least once before using it in your random query. e.g. just run a query like this first: SELECT RAND(), RAND(), RAND(); BTW, what version of MySQL are you using? Try running your query multiple times from the command line and it will probably work correctly after the first time. So either upgrade MySQL or use my above workaround. :-) Hope that helps. Matt - Original Message - From: Jay Paulson Sent: Thursday, August 28, 2003 2:57 PM Subject: Random Selects Hello- I'm trying to get random information out of my table and the query I'm using keeps returning the same row every time. In the table I have 3 rows and I want to choose at random in the sql which row to return. Here's an example query I have. SELECT * FROM banner ORDER BY rand() asc limit 0,1 This returns the first row in the table every time. What's strange is that I have a query for a table that has about 500 rows in it and it works fine with the exact same syntex (except the the table is different). Is there anything I'm doing wrong? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Random Selects
I have a table called testimonials: +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | id | tinyint(3) | | PRI | NULL| auto_increment | | can_use | tinyint(1) | | | 0 || | name | varchar(64) | | | 0 || | location | varchar(64) | | | 0 || | quote| text| | | || +--+-+--+-+-++ That contains 20 or so customer testimonial comments. I want to randomly display 3-4 of them every time a page loads (using PHP). Is there some type of random selction available within MySQL, as in: SELECT RANDOM(id),name,location,quote FROM testimonials WHERE can_use=1 LIMIT 3; Or something to that effect or am I better served finding some way in PHP to do this? Thanks. - 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: Random Selects
At 12:33 -0600 3/8/03, Darren Young wrote: I have a table called testimonials: +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | id | tinyint(3) | | PRI | NULL| auto_increment | | can_use | tinyint(1) | | | 0 || | name | varchar(64) | | | 0 || | location | varchar(64) | | | 0 || | quote| text| | | || +--+-+--+-+-++ That contains 20 or so customer testimonial comments. I want to randomly display 3-4 of them every time a page loads (using PHP). Is there some type of random selction available within MySQL, as in: SELECT RANDOM(id),name,location,quote FROM testimonials WHERE can_use=1 LIMIT 3; Or something to that effect or am I better served finding some way in PHP to do this? ORDER BY RAND() LIMIT 3 ought to do it, unless your version of MySQL is older than 3.23.2. Thanks. - 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: Random Selects
3.23.54 on Linux. And it worked. Thx. Seems to be not so random, but then with more records to sample from the randomness would increase. True? -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Saturday, March 08, 2003 2:33 PM To: Darren Young; [EMAIL PROTECTED] Subject: Re: Random Selects At 12:33 -0600 3/8/03, Darren Young wrote: I have a table called testimonials: +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | id | tinyint(3) | | PRI | NULL| auto_increment | | can_use | tinyint(1) | | | 0 || | name | varchar(64) | | | 0 || | location | varchar(64) | | | 0 || | quote| text| | | || +--+-+--+-+-++ That contains 20 or so customer testimonial comments. I want to randomly display 3-4 of them every time a page loads (using PHP). Is there some type of random selction available within MySQL, as in: SELECT RANDOM(id),name,location,quote FROM testimonials WHERE can_use=1 LIMIT 3; Or something to that effect or am I better served finding some way in PHP to do this? ORDER BY RAND() LIMIT 3 ought to do it, unless your version of MySQL is older than 3.23.2. Thanks. - 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: Random Selects
At 15:32 -0600 3/8/03, Darren Young wrote: 3.23.54 on Linux. And it worked. Thx. Seems to be not so random, but then with more records to sample from the randomness would increase. True? Probably. However, if you check the change notes in the manual for 3.23.56, you'll see that RAND() initialization was modified to be more random for new connections. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Saturday, March 08, 2003 2:33 PM To: Darren Young; [EMAIL PROTECTED] Subject: Re: Random Selects At 12:33 -0600 3/8/03, Darren Young wrote: I have a table called testimonials: +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | id | tinyint(3) | | PRI | NULL| auto_increment | | can_use | tinyint(1) | | | 0 || | name | varchar(64) | | | 0 || | location | varchar(64) | | | 0 || | quote| text| | | || +--+-+--+-+-++ That contains 20 or so customer testimonial comments. I want to randomly display 3-4 of them every time a page loads (using PHP). Is there some type of random selction available within MySQL, as in: SELECT RANDOM(id),name,location,quote FROM testimonials WHERE can_use=1 LIMIT 3; Or something to that effect or am I better served finding some way in PHP to do this? ORDER BY RAND() LIMIT 3 ought to do it, unless your version of MySQL is older than 3.23.2. Thanks. - 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
