Re: Age old order by rand() issue
> SELECT storage_path, image_md5, id > FROM images > JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM images WHERE approved = > 1)) AS id) AS r2 USING (id) >WHERE approved = 1; > > I really do not get this, SELECT CEIL(RAND() will always return 1 will it > not? Any idea why I get an empty result set at times? http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand So CEIL(RAND() ) would always be 1. My guess is you have is that you have hole(s) in your data set. For the record you are not doing CEIL(RAND() ), your doing CEIL(RAND() * (SELECT MAX(id) FROM images WHERE approved => 1)) -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Age old order by rand() issue
Hi, Responses inline On Thu, May 1, 2008 at 3:11 PM, Scott Haneda <[EMAIL PROTECTED]> wrote: > List search seems to return 0 results, and I am a bit stumped. > > Getting a more optimized order by random with 1 record... > I found a snip online that works, but seems to return an empty on occasion, > and I am not sure why: > > SELECT storage_path, image_md5, id > FROM images > JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM images WHERE approved = > 1)) AS id) AS r2 USING (id) >WHERE approved = 1; > > I really do not get this, SELECT CEIL(RAND() will always return 1 will it > not? Any idea why I get an empty result set at times? http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand So CEIL(RAND() ) would always be 1. My guess is you have is that you have hole(s) in your data set. > > I then managed to rig this together: > SELECT * FROM images AS t > JOIN (SELECT CEIL(MAX(id)*RAND()) AS id > FROM images WHERE approved = 1) AS x ON t.id >= x.id LIMIT 1; > > This works, but I get an odd result, in that the id column is listed twice, > once at the beginning, where it is in the table, and once at the end. > Duplicate values of course. Using a star is less than a great idea. You are a second id from CEIL(MAX(id)*RAND()) AS id being joined in. Specify the columns you want... > If I narrow the * to a real called select, such as > SELECT id, storage_path, image_md5 FROM images AS t > JOIN (SELECT CEIL(MAX(id)*RAND()) AS id > FROM images WHERE approved = 1) AS x ON t.id >= x.id LIMIT 1; > > -> FROM images WHERE approved = 1) AS x ON t.id >= x.id LIMIT 1; > ERROR 1052 (23000): Column 'id' in field list is ambiguous Mysql wants you to specify what table you want the id from. Meaning from x or t... SELECT t.id, storage_path, image_md5 FROM images AS t JOIN (SELECT CEIL(MAX(id)*RAND()) AS id FROM images WHERE approved = 1) AS x ON t.id >= x.id LIMIT 1; I have seen nicer fast random row implement, but that will work. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Age old order by rand() issue
List search seems to return 0 results, and I am a bit stumped. Getting a more optimized order by random with 1 record... I found a snip online that works, but seems to return an empty on occasion, and I am not sure why: SELECT storage_path, image_md5, id FROM images JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM images WHERE approved = 1)) AS id) AS r2 USING (id) WHERE approved = 1; I really do not get this, SELECT CEIL(RAND() will always return 1 will it not? Any idea why I get an empty result set at times? I then managed to rig this together: SELECT * FROM images AS t JOIN (SELECT CEIL(MAX(id)*RAND()) AS id FROM images WHERE approved = 1) AS x ON t.id >= x.id LIMIT 1; This works, but I get an odd result, in that the id column is listed twice, once at the beginning, where it is in the table, and once at the end. Duplicate values of course. If I narrow the * to a real called select, such as SELECT id, storage_path, image_md5 FROM images AS t JOIN (SELECT CEIL(MAX(id)*RAND()) AS id FROM images WHERE approved = 1) AS x ON t.id >= x.id LIMIT 1; -> FROM images WHERE approved = 1) AS x ON t.id >= x.id LIMIT 1; ERROR 1052 (23000): Column 'id' in field list is ambiguous I can not seem to get past that error, and would like to call just the columns I want. Or, if someone has a better random record return select, I am all ears. Thanks so much. -- Scott [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
