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

Selecting a Random Record from Each Category in MySQL

This database query involves selecting a random record from each category in a table. The provided table contains an "Items" table with columns for "id," "name," and "category." Each item belongs to one of seven categories represented in the "Categories" table with "id" and "category" columns.

To approach this query effectively, you can use a combination of joins and the RAND() function. Here's the query that accomplishes the task:

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 first joins the "Categories" and "Items" tables on the "category" column to retrieve all items and their corresponding categories. The ORDER BY RAND() clause applies a random ordering to the results. Subsequently, the query is wrapped in a subquery and grouped by the "cid" column. This grouping ensures that each category is limited to a single random record.

Note that performance optimization may be necessary for large tables. If the query runs slowly, consider using MySQL's TEMPORARY TABLES or creating an index on the "category" column.

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