MySQL Query Help - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: MySQL Query Help (/showthread.php?tid=9834) |
MySQL Query Help - El Forum - 07-10-2008 [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 MySQL Query Help - El Forum - 07-10-2008 [eluser]xwero[/eluser] loose the GROUP BY and use LIMIT 1 instead that should do the trick. MySQL Query Help - El Forum - 07-10-2008 [eluser]walterblaurock[/eluser] Doing that simply returns 1 random song. I want to get 1 random song for every single band in the table. MySQL Query Help - El Forum - 07-10-2008 [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 MySQL Query Help - El Forum - 07-10-2008 [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. |