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

Question

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

From the table A → (name it count_a), you can select no more than 25 rows

From table B → (count_b) you can select no more than 40 rows

Any number of rows can be selected from table C (count_c), but the number must 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 request is too slow and does not always give me 100 rows.

Answer 1

Try adding WHERE ROWNUM <= 100 to the last selection:

 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; 

You can also 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) 

Answer 2

Try it,

 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 - ((SELECT count(*) FROM table_a WHERE ROWNUM <= 25) + (SELECT count(*) FROM table_b WHERE ROWNUM <= 40)) ); 

Answer 3

Technically, you will need to do something similar to ensure that you will always get rows from TABLE_A and TABLE_B if they exist:

 SELECT * FROM ( SELECT * FROM ( SELECT 'A' t, TABLE_A.* FROM TABLE_A WHERE ROWNUM <= 25 UNION ALL SELECT 'B' t, TABLE_B.* FROM TABLE_B WHERE ROWNUM <= 40 UNION ALL SELECT 'C' t, TABLE_C.* FROM TABLE_C ) ORDER BY t ) WHERE ROWNUM <= 100; 

This is due to the fact that the optimizer is allowed to run subqueries in any order that he likes - for example. parallel.

As far as performance is concerned, I suspect that sort op will not add too much time to the run time, because it still sorts no more than 100 rows.

Answer 4

Yes ... Execution speed is a nightmare for every developer or programmer ... In this case, you can try ... I think it will speed up your request more

 DECLARE @Table_A_Row_Count INT, @Table_B_Row_Count INT,@RemainCount INT=0 --Getting count of primary tables SELECT @Table_A_Row_Count= COUNT(1) FROM TABLE_A SELECT @Table_B_Row_Count= COUNT(1) FROM TABLE_B --Calculating remaining colections IF @ Table_A_Row_Count+@Table _B_Row_Count<100 BEGIN SET @RemainCount=100-(@ Table_A_Row_Count+@Table _B_Row_Count) END ELSE BEGIN --You might do somthing like this if First --and second table covering 100 rows SET @ Table_B_Row_Count=100-@Table _A_Row_Count END --Finaly getting 100 rows SELECT top @Table_A_Row_Count * FROM TABLE_A UNION ALL SELECT top @Table_B_Row_Count * FROM TABLE_B UNION ALL SELECT Top @RemainCount * FROM TABLE_C