I have 2 problems i'm trying to solve.

1. How to select a random picture from each group of pictures but not repeat the group.

I have 2 tables
- Pictures : contains Id, Thumb (link to the picture location, Exclude, GroupNameId
- GroupName, contains Id, Group, Exclude, newgroup (to show a NEW pic)

The current query is :
SELECT groupname.Group, pictures.Thumb, groupname.ID, groupname.newgroup
FROM rascal.pictures, rascal.groupname
WHERE pictures.Exclude=0 AND groupname.exclude=0 AND pictures.groupnameid = groupname.id and Thumb <> ''
ORDER BY RAND()

This brings back random pictures but sometimes (almost always) the groups are duplicated.

I'd like something to only bring back one random picture per group.

I tried this

select * from rascal.pictures a JOIN rascal.GroupName b on a.groupnameid=b.id Where a.exclude <> 1 and Thumb<>"" Group by b.id order by Rand()

but this brings back the same picture in each group each time, which is not what i want.

Does anyone have any idea how to acheive this is MYSQL ?

2. UK Number One Hits.
I've decided in my wisdom that it would be quite cool to be able to tell someone what was number on the day they were born (by entering a date) ... easy peazy, but I want to also display what was number one on each birthday until the present day.

The file contains the date the record became number one NOT each date for every year.

To get the birth date would be something like

SELECT * from numberones where numberonedate < xdate ORDER by numberonedate DESC LIMIT 1

but how would I return each seperate year into an ASP page and display it ?

Any advise very welcome.

Thanks

Dave

I'm not a MYSQL guru, but I'll try to help:

For #1

Solution seems obvious at first glance, but everything I tried didn't work as expected. There's DISTINCT and GROUP BY which shoud limit the number of returned rows, but DISTINCT cannot be applied on a single column: it is applied on the entire row. GROUP BY need to be placed before ORDER BY rand, so it returns the pictures, and then randomize the result. Not good either.

If you're coding in PHP or something else, you could do:

SELECT DISTINCT id from groupname where exclude=0 ORDER BY rand();

which will return all the groupid you want, without repetition. Then loop in the PHP or whatever you're using and do:

SELECT groupname.Group, pictures.Thumb, groupname.ID, groupname.newgroup
FROM rascal.pictures, rascal.groupname
WHERE pictures.Exclude=0 AND pictures.groupnameid = "prevously_fetched_groupid_scrolled_by_loop" and Thumb <> ''
ORDER BY RAND() LIMIT 1;

Which should return 1 picture each time this query is called: for each group.


Sure, this isn't nice, but I looked on google and I just can't find anything better.

Ah, and if you have MySQL 4.1 or later, it seems you could try something like (not verified here):

select *
from (
SELECT groupname.Group, pictures.Thumb, groupname.ID, groupname.newgroup
FROM rascal.pictures, rascal.groupname
WHERE pictures.Exclude=0 AND groupname.exclude=0 AND pictures.groupnameid = groupname.id and Thumb <> ''
ORDER BY RAND()
)
GROUP BY groupname.id;