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]
