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

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

CodePudding user response：

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)

CodePudding user response：

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)```