CODEWITHSUNDEEP
mysqlsqlrandomsql-order-by
djt
djt

Reputation: 7535

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 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.

Upvotes: 12

Views: 12270

Answers (6)

Bogdan Haidu
Bogdan Haidu

Reputation: 125

Starting from mysql8 you can solve this by using Windows functions, avoiding the extra join condition with category table

https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html

     SELECT * FROM (
     SELECT FIRST_VALUE(id) OVER(PARTITION BY category ORDER BY RAND()) AS first_id FROM items
     Order BY RAND()) AS r
     GROUP BY first_id

Upvotes: 0

Ricardo
Ricardo

Reputation: 31

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

select * from 
  (select category, id, name from categories order by rand()) as tab 
  group by 1

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272146

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.

Upvotes: 11

Vikram
Vikram

Reputation: 4190

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/

Upvotes: 0

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

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

Upvotes: 0

Ezra Free
Ezra Free

Reputation: 754

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`

Upvotes: -1

Related Questions