How to combine four select queries which select an equal number of rows randomly into one?

Question

The following is my db table:

 id category_id name
--------------------
  1     4        A
  2     5        B
  3     6        C

I have four simple select queries which pull 15 rows by random from specific categories:

select * from table where category_id = 4 order by rand() limit 15;
select * from table where category_id = 5 order by rand() limit 15;
select * from table where category_id = 6 order by rand() limit 15;
select * from table where category_id = 7 order by rand() limit 15;

I want to combine them into a single query rather than four separate queries. I've tried using the UNION operator but it wasn't pulling 15 rows EQUALLY from each category:

(
    select * from table where category_id = 4 
    union 
    select * from table where category_id = 5
    union 
    select * from table where category_id = 6
    union 
    select * from table where category_id = 7 
) order by rand() limit 60;

How can I achieve this? Or, do I have to run separate queries?

^{I've tagged Laravel because I'm using Laravel as the backend and maybe Eloquent has a smarter way to achieve this.}

https://stackoverflow.com/questions/13917558/laravel-eloquent-or-fluent-random-row and this https://stackoverflow.com/questions/29115385/how-to-make-laravel-eloquent-in-query — flakerimi, Nov 29 '21 at 13:48
It should be something DB::table('table')->whereIn('category_id', [4,5,6,7])->inRandomOrder()->limit(15)->get(); — flakerimi, Nov 29 '21 at 13:50
Are you using MySQL 8.0? You may be able to do this with window functions. — Bill Karwin, Nov 29 '21 at 13:52
@flakerimi Those don't solve my original question. That gets 15 rows from specific categories in random order. I need 15 random rows from EACH of those categories (total 60 rows). — Script47, Nov 29 '21 at 13:54
@BillKarwin I am indeed using v8. I'll look into window functions as I've not come accross them before. — Script47, Nov 29 '21 at 13:59

score 1 · Accepted Answer · answered Nov 29 '21 at 13:46

1

Have you tried this one?:

    (select * from table where category_id = 4 ORDER BY rand() LIMIT 15)
    union all
    (select * from table where category_id = 5 ORDER BY rand() LIMIT 15)
    union all 
    (select * from table where category_id = 6 ORDER BY rand() LIMIT 15)
    union all
    (select * from table where category_id = 7 ORDER BY rand() LIMIT 15)

answered Nov 29 '21 at 13:46

Michal

132
12

Ah, I just figured it out and switched tabs to find your answer. The only difference is I'm intentionally not using `union all`. – Script47 Nov 29 '21 at 13:53

Hoshani · Answer 2 · 2021-11-29T14:10:26.953

0

you could use CTE and ROW_NUMBER() as such

WITH CTE AS (
SELECT id, 
           category_id, 
           ROW_NUMBER() OVER(PARTITION BY category_id 
                                 ORDER BY RAND() DESC) AS rank
      FROM table)
 SELECT *
   FROM CTE
 WHERE rank <= 15 AND category_id IN (4,5,6,7)```

edited Nov 29 '21 at 14:10

answered Nov 29 '21 at 13:53

Hoshani

746
1
10
27

1

I'm not seeing the randomness in this? – P.Salmon Nov 29 '21 at 13:56
1

You should use `OVER(PARTITION BY category_id ORDER BY RAND())` – Bill Karwin Nov 29 '21 at 14:03

How to combine four select queries which select an equal number of rows randomly into one?

2 Answers2