Re: Age old order by rand() issue

["Rob Wultsch" <wultsch@xxxxxxxxx>]

Hi,
Responses inline....

On Thu, May 1, 2008 at 3:11 PM, Scott Haneda <talklists@xxxxxxxxxx> 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
wultsch@xxxxxxxxx
wultsch (aim)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=royale@xxxxxxxxxx