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]
