MYSQL Select One Random record from each Category

Question

I have a database with an Items table that looks something like this:

id
name
category (int)

There are several hundred thousand records. Each item can be in one of 7 different categories, which correspond to a categories table:

id
category

I want a query that chooses 1 random item, from each category. Whats the best way of approaching that? I know to use Order By rand() and LIMIT 1for similar random queries, but I've never done something like this.

Answer 1

Please note: in the following example I am assuming your table is named "items" not "Items" because you also said the other table was named "categories" (second table name not capitalized).

The SQL for what you want to do would roughly be:

`SELECT items.id AS item_id,
items.name AS item_name,
items.category AS item_category_id,
categories.id AS category_id,
categories.category AS category_name
FROM items, category
WHERE items.category = categories.id
ORDER BY rand()
LIMIT 1`

Answer 2

Try this

SELECT id, name, category from Items where
( 
 select count(*) from Items i where i.category = Items.category 
 GROUP BY i.category ORDER BY rand()
) <= 1

REF: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

Answer 3

This query returns all items joined to categories in 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()

To restrict each category to one, wrap the 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

Note that when a query has both GROUP BY and ORDER BY clause, the grouping is performed before sorting. This is why I have used two queries: the first one sorts the results, the second one groups the results.

I understand that this query isn't going to win any race. I am open to suggestions.

Answer 4

Here is a simple solution. Let suppose you have this table.

id  name    category
1   A       1
2   B       1
3   C       1
4   D       2
5   E       2
6   F       2
7   G       3
8   H       3
9   I       3

Use this query

select
  c.id,
  c.category,
  (select name from category where category = c.category   group by id order by rand() limit 1) as CatName
from category as c
group by category