How to Select One Random Record from Each Category in MySQL?

Selecting a Single Random Record from Each Category in MySQL

To retrieve a random item from each category in the specified database, a two-stage approach is recommended. First, use a query to combine the items and categories tables in a 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();

This query will display all items joined to their respective categories in a random sequence. To limit the selection to one item per category, nest the previous query within a partial GROUP BY statement:

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 partial GROUP BY ensures that each category only contains one randomly selected item. It's important to note that the ORDER BY clause must be applied before the GROUP BY clause for this approach to work correctly.

While this method may not be the fastest, it offers a reliable way to select a single random record from each category in your MySQL database.

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