How to select a random row with a group by clause?

Question

I have the following table

SQLFiddle

What I'm attempting to do is to select three random images but to make sure that no two images have the same object, what I attempted to do is to do a GROUP BY along with an ORDER BY rand() but that is failing as it is always giving me cat1.jpg, dog1.jpg, box1.jpg (All images whose path ends with 1 and not the others)

The fiddle includes the query I ran and how it is not working.

Answer 1

You can't get a random image as MySQL always returns that data based on the time of insert (first come, first serve), i.e. internal order.

But you can get a random result using following approach (fiddle):

SELECT images.image_path AS image_path, objects.name 
FROM objects 
LEFT JOIN 
 ( 
   SELECT object_id, 
      SUBSTRING_INDEX(GROUP_CONCAT(image_path order by rand()), ',', 1) AS image_path
   FROM images
   GROUP BY object_id
 ) as images
ON images.object_id = objects.id 
GROUP BY objects.name 

If there's a restrictive WHERE-condition on the objects table you might get a better performance when you join first and the GROUP_CONCAT.

Answer 2

I think this should do:

ORDER BY random() LIMIT 1

Answer 3

What you need is a Random aggregate function. Usually there are no such functions in the current RDBMSs.

Similar question has been asked.

So the basic idea is shuffle the elements, then group by, and then for every group just select the first row for every group. If we modify one of answers provided on the link we get this.

select object_id, name, image_path
from
  (SELECT images.image_path AS image_path, objects.id AS object_id, objects.name
  FROM objects LEFT JOIN images ON images.object_id = objects.id
  ORDER BY RAND()) as z
group by z.object_id, z.name