MYSQL Select One Random record from each Category
This query returns all items joined to categories in random order:
SELECT
c.id AS cid, c.category, i.id AS iid, i.name
FROM categories c
INNER JOIN items i ON c.id = i.category
ORDER BY RAND()
To restrict each category to one, wrap the query in a partial GROUP BY
:
SELECT * FROM (
SELECT
c.id AS cid, c.category, i.id AS iid, i.name
FROM categories c
INNER JOIN items i ON c.id = i.category
ORDER BY RAND()
) AS shuffled_items
GROUP BY cid
Note that when a query has both GROUP BY
and ORDER BY
clause, the grouping is performed before sorting. This is why I have used two queries: the first one sorts the results, the second one groups the results.
I understand that this query isn't going to win any race. I am open to suggestions.