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.

Solution 1

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

Solution 2

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.

Comments

[techjp]

Your link seems to go to an unrelated question that asks about table aliases. I've been trying to get this sample code to work and have adapted it to my table setup but it returns exactly the same data each time. The data returned is the first row for each grouped item rather than a random row. Has this functionality changed in later versions of MySQL?

[techjp]

This works beautifully! It does have some performance issues on very large tables but it actually returns randomized data from a group. The other reply (that has more votes!) didn't return random data for me, I've just spent hours trying to get it to work. THANK YOU!