How to Randomly Select One Item from Each Category in MySQL?

Random Item Selection from Distinct Categories using MySQL

In a database scenario involving an "Items" table with a categorization column, the task of randomly selecting a single item from each category poses a challenge. To address this, let's explore the following approaches utilizing MySQL queries:

Method 1: Inner Join and Partial Grouping

This query retrieves all items joined with categories sorted randomly:

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 a single item, we wrap this 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

Considerations

Note that the grouping is performed before sorting since the query includes both a GROUP BY and ORDER BY clause. Therefore, the outer query groups the results after the inner query sorts them. This two-query approach ensures that each category contains only one random item.

While this query provides a solution, it's important to acknowledge its potential efficiency limitations. We welcome any suggestions for performance optimizations.

The above is the detailed content of How to Randomly Select One Item from Each Category in MySQL?. For more information, please follow other related articles on the PHP Chinese website!