How to Efficiently Query One Random Item from Each Category in MySQL?

Query Random Items from Each MYSQL Category

Database systems encounter scenarios where selecting a random record from each category is common. Consider a database with an Items table containing items belonging to different categories, each with its unique ID. And a separate Categories table provides categories' names and ID.

To select a single random item per category, we can leverage a combination of grouping and randomization.

First, we join the Items and Categories tables on category ID to relate items with their respective categories:

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

This yields a result set containing all items with their associated category information.

Now, to randomize the item selection, we add ORDER BY RAND():

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 limit each category to one item, we utilize 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

This query effectively groups the randomly sorted items by category ID and selects the first item in each group. The grouping operation occurs before the sorting, so the random order is preserved within each category.

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