Re: Age old order by rand() issue
On Fri, May 2, 2008 at 6:58 AM, Scott Haneda <[EMAIL PROTECTED]> wrote: > > > I have seen nicer fast random row implement, but that will work. > > > Do you happen to have a snip of it, the one I have seems to lean pretty > heavy as far as I can tell, and on occasion, though rare, also sends me an > empty result set. > -- > Scott > [EMAIL PROTECTED] > You should not be getting empty results with the second/third query. The reason you were (probably) previously getting empty results with the first query was because you were doing the join using USING (aka =) rather than >= . You were also doing a WHERE clause on that could have removed the random result. My only problem with what you are using is that it is more likely to give a large results than a small one. Take a look at the http://jan.kneschke.de/projects/mysql/order-by-rand/ You probably do not need this. If you have a large data set, you probably don't want this. -- 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
I have seen nicer fast random row implement, but that will work. Do you happen to have a snip of it, the one I have seems to lean pretty heavy as far as I can tell, and on occasion, though rare, also sends me an empty result set. -- Scott [EMAIL PROTECTED] -- 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
> 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]
