aspacecodyssey Posted September 26, 2011 Share Posted September 26, 2011 Server version: 5.1.37 I have 2 tables: 'artists' and 'images'. They are set up as follows: artists: id, name images: id, image, artist The 'artist' column in the 'images' table corresponds to the 'id' column from the 'artists' table. So, some sample data from 'images': 1 file1.jpg 4 2 file2.jpg 5 3 file3.jpg 4 4 file4.jpg 6 5 file5.jpg 6 6 file6.jpg 5 7 file7.jpg 6 I would like to SELECT 1 random row from the images table using GROUP BY (I think) such that 1 random image for each artist is selected. If there are 30 artists, 30 rows should return, random each time. My attempt was: SELECT images.image, images.artist, artists.id FROM images JOIN artists ON images.artist=artists.id GROUP BY artists.id ORDER BY RAND() But this is simply selecting the first image (by id) for each artist and ordering randomly *after* the fact. Any ideas? Quote Link to comment https://forums.phpfreaks.com/topic/247904-select-a-random-row-with-group-by/ Share on other sites More sharing options...
aspacecodyssey Posted September 26, 2011 Author Share Posted September 26, 2011 It seems like I need to select the entire table of images, i.e. all images, then sort them (ORDER BY), *then* GROUP BY. Maybe a nested list? Quote Link to comment https://forums.phpfreaks.com/topic/247904-select-a-random-row-with-group-by/#findComment-1272970 Share on other sites More sharing options...
oyster12 Posted September 27, 2011 Share Posted September 27, 2011 id is primary key in artist, so you just find one record try this: GROUP BY images.artist Quote Link to comment https://forums.phpfreaks.com/topic/247904-select-a-random-row-with-group-by/#findComment-1273073 Share on other sites More sharing options...
aspacecodyssey Posted September 27, 2011 Author Share Posted September 27, 2011 Thanks for the reply. I wound up figuring it out! It was a nested SELECt as I suspected. I had never done one before. This was the solution: SELECT * FROM ( SELECT images.image, images.artist, artists.id, artists.firstname, artists.lastname FROM images JOIN artists ON images.artist=artists.id ORDER BY RAND() ) AS T GROUP BY id ORDER BY lastname This grabs 1 random row for each artist in the images table, then sorts the result by artist's lastname. Quote Link to comment https://forums.phpfreaks.com/topic/247904-select-a-random-row-with-group-by/#findComment-1273113 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.