CODEWITHSUNDEEP
mysql
Jackstick
Jackstick

Reputation: 89

MySql taking random element from distinct random category

I have a table images, which has rows i_id | imgpath | category

I would like to choose 60 distinct categories randomly and a random image from each category.

So far I have a query

SELECT DISTINCT(category), i_id, imgpath FROM images ORDER BY RAND() LIMIT 60

But it sometimes produces more than one image from the same category.

Upvotes: 2

Views: 27

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can do so by using GROUP_CONCAT to have a comma separated list of imgpath for each category so that images for each category will be ordered randomly then SUBSTRING_INDEX over comma separated list of imgpath to pick one image 

SELECT category, 
SUBSTRING_INDEX(
GROUP_CONCAT(imgpath ORDER BY RAND())
,',',1) imgpath
FROM images 
GROUP BY category 
ORDER BY RAND() 
LIMIT 60

Upvotes: 2

Related Questions