Question 186248 in Mysql Question posted in
The official MySQL documentation can be found here.

Select random row for each category in mysql

I have a table like this

ID category product
1 1 p1
2 1 p2
3 1 p3
4 1 p4
5 2 p5
6 2 p6
7 2 p7
8 3 p8
9 3 p9
10 3 p10

I am trying to select one random product for each category.

result like this :

category product
1 p3
2 p7
3 p8

i tried with this query, gives me random but not for each category without duplicates
SELECT
category, product
FROM
table
WHERE
category IN (1,2,3)
ORDER BY RAND()
LIMIT N

Tags:

3

Answers


  1. 0

    Perhaps try the following using a sub-query correlated for each category:

    select category, (
  select product 
  from t t2 
  where t.category = t2.category
  order by rand() limit 1
) product
from t
group by category;
    Login or Signup to reply.
  2. 0

    You can do it as follows :

    SELECT t.category, t.product
FROM mytable t
inner join (
  select category, FLOOR((RAND() * (max(ID)-min(ID)+1))+min(ID)) as randomID
  from mytable
  group by category
) as s on s.category = t.category and s.randomID = t.ID

    Demo here

    Login or Signup to reply.
  3. 0

    Another approach could be to make use of PARTITION BY:

    with tmp as (
select  row_number() over (partition by category 
                              order by RAND() 
                            )      as rn,
        category,
        product
from t
)
select  category , 
        product  
from tmp
where rn = 1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search