Chris Chris - 14 days ago 6
MySQL Question

MySQL LIMIT up to a certain number - depending on previous subquery

I am trying to build an SQL query that randomly selects entries from different tables, up to a certain number.

Let's say I have 3 tables A,B and C. I want to select 10 rows total from A, B and C combined.

Now i want to randomly select 2 entries from A, up to 6 entries from B (depending on how many were retrieved from A. If A does not have any rows, I want to get 6 rows from B. If A returned 1 row, i want to get 5 rows from B and so on..). Then I want to fill the remaining entries (between 0 and 10, depending on the previous subqueries) with randomly selected rows from C.
So far I have tried:

select * from (
(SELECT * FROM A ORDER BY RAND() LIMIT 2)
UNION
(SELECT * FROM B ORDER BY RAND())
limit 4) a


Unfortunately, as soon as I union both subqueries the random ordering from the second query is lost and I always retrieve the same first 4 rows from B.
Is this possible using just SQL or do I have to do multiple queries and union them programmatically?

Best regards

Answer

Use CTEs so that you can get the counts of the rows that were selected from tables A and B. Like this:

with a_rows as (
    select * from table_a order by random() limit 2
    ),
b_rows as (
    select * from table_b order by random() limit 6 - (select count(*) from a_rows)
    ),
c_rows as (
    select * from table_c order by random() limit 10 - (select count(*) from b_rows)

select * from a_rows
union
select * from b_rows
union
select * from c_rows;

Note that CTEs are a relatively recent addition to MySQL's feature set.

Alternatively, you could use separate queries instead of CTEs.

Comments