Welcome Guest, Not a member yet? Register   Sign In
MySQL Query Help

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,


loose the GROUP BY and use LIMIT 1 instead that should do the trick.

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

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.

Theme © iAndrew 2016 - Forum software by © MyBB