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

Randomizing Database Selections Across Multiple Categories

The challenge presented here is to retrieve a single random record from each category within a database. The Items table houses these records, each assigned to a category specified by a corresponding Categories table.

To approach this task, one can leverage MySQL's inherent randomness with the RAND() function and LIMIT 1 clause. However, the unique aspect here is the need to select a random record for each distinct category.

The following query elegantly tackles this challenge:

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's structure enables the retrieval of one random record from each category:

• The main query uses a subquery to retrieve all items joined with their respective categories, sorted randomly.
• The subquery serves as a foundation for the subsequent filtering employed by the partial GROUP BY. This ensures one record per category by grouping the "shuffled_items" based on the category ID (cid).

While this approach may not boast exceptional speed, it effectively addresses the challenge of random record selection across multiple categories. Alternative suggestions for optimization are welcome, further enhancing the utility of this query.

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