SEO

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
talklists@newgeo.com

> 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/...#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
wultsch@gmail.com
wultsch (aim)

Hi,
Responses inline....

On Thu, May 1, 2008 at 3:11 PM, Scott Haneda <talklists@newgeo.com> 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/...#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@gmail.com
wultsch (aim)

> 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
talklists@newgeo.com

On Fri, May 2, 2008 at 6:58 AM, Scott Haneda <talklists@newgeo.com> 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
> talklists@newgeo.com
>

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
wultsch@gmail.com
wultsch (aim)