MySQL Query Help

[eluser]walterblaurock[/eluser]
Hi. I have been trying to solve this issue, and similar issues, for many months now, but I have no clue where to go for help. I hope this is the right place. If you know of a better place, please let me know! Thanks.

The table I am running the query on is set up with a column for band_id and a column for song_id. The idea is to get a random song_id for each band_id in the table. Take a look at the following mock up:

band_id song_id

1 ........... 1
1 ........... 2
2 ........... 3
3 ........... 4
3 ........... 5

So, I would like to get a random song (just one) from bands 1, 2, and 3 (e.g. I'd like to end up with song_ids: 1, 3, 5 or 2, 3, 4, etc)

The query I have been using up to this point is as follows: SELECT band_id,song_id FROM songs GROUP BY band_id ORDER BY RAND()

The problem with running this query is that the same 3 songs always turn up--always the first song for each band. So I might end up with the result array 1,3,5 or 3,5,1, but they always contain those 3 songs. I would like to be able to get a random song for each band each time.

If you have any ideas, or need some clarification, please let me know.

Thanks for your time,

-Walter

[eluser]xwero[/eluser]
loose the GROUP BY and use LIMIT 1 instead that should do the trick.

[eluser]walterblaurock[/eluser]
Doing that simply returns 1 random song. I want to get 1 random song for every single band in the table.

[eluser]Colin Williams[/eluser]
hrm.. SELECT band_id,song_id FROM songs GROUP BY band_id ORDER BY band_id, RAND()

Maybe... not sure I've ever done this type of thing

[eluser]walterblaurock[/eluser]
That query still returns only ever returns the first song from each band. I wonder if there is a way to change which element gets selected by the GROUP BY statement, instead of it just assuming I want the first song.