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