Select a random row with GROUP BY

[aspacecodyssey]

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?

[aspacecodyssey]

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?

[oyster12]

id is primary key in artist, so you just find one record

try this:

GROUP BY images.artist

[aspacecodyssey]

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.