How can I collectively select 100 rows from 3 different tables?

Question

I have 3 tables containing similar rows of data. I need to select 100 rows from all of the three tables with the following conditions:

No more than 25 rows can be selected from Table A --> (name it count_a)

No more than 40 rows can be selected from Table B --> (count_b)

Any number of rows can be selected from Table C (count_c) but the number should be count_c = 100 - (count_a + count_b)

Here is what I tried:

SELECT * FROM
(
    SELECT * FROM TABLE_A WHERE ROWNUM <= 25
    UNION ALL 
    SELECT * FROM TABLE_B WHERE ROWNUM <= 40
    UNION ALL
    SELECT * FROM TABLE_C 
) WHERE ROWNUM <=100

But the query is too slow and does not always give me 100 rows.

Answer 1

Try to add WHERE ROWNUM <= 100 to the last select:

SELECT * FROM
(
    SELECT TABLE_A.*, 1 as OrdRow FROM TABLE_A WHERE ROWNUM <= 25
    UNION ALL 
    SELECT TABLE_B.*, 2 as OrdRow FROM TABLE_B WHERE ROWNUM <= 40
    UNION ALL
    SELECT TABLE_C.*, 3 as OrdRow  FROM TABLE_C WHERE ROWNUM <= 100
) WHERE ROWNUM <=100
  ORDER BY OrdRow;

Also you can try:

SELECT * FROM TABLE_A WHERE ROWNUM <= 25
UNION ALL 
SELECT * FROM TABLE_B WHERE ROWNUM <= 40
UNION ALL
SELECT * FROM TABLE_C WHERE ROWNUM <= 
       100
       -
       (select count(*) TABLE_A WHERE ROWNUM <= 25)
       -
       (select count(*) TABLE_B WHERE ROWNUM <= 40)