Mysql Select One Random Record From Each Category

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 ca

Solution 1:

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
ORDERBY RAND()

To restrict each category to one, wrap the query in a partialGROUP 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
    ORDERBY RAND()
) AS shuffled_items
GROUPBY 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.

Solution 2:

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

id  name    category
1A12B13   C       14   D       25   E       26   F       27   G       38   H       39I3

Use this query

select
  c.id,
  c.category,
  (select name from category where category = c.category   groupby id order byrand() limit 1) as CatName
from category as c
groupby category

Solution 3:

Try this

SELECT id, name, category from Items where
( 
 select count(*) from Items i where i.category = Items.category 
 GROUPBY i.category ORDERBY rand()
) <= 1

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

Solution 4:

Change order of the original table (random order), before final select:

select * from 
  (select category, id, name from categories order byrand()) as tab 
  groupby 1

Solution 5:

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
ORDERBY rand()
LIMIT 1`