Count Rows in One Table and Use that Variable in Another (Union)

Question

I am interested in accomplishing the following: I have two tables from which I need to select a total of 20 patients from a combination of two patient categories:

I need UP TO the first five examples of CATEGORY A from the first table, let's say surgical patients (may be anywhere from 0 to 5) I need the remainder (20-n) to come from CATEGORY B from the second table, let's say patients of any other type.

I have no problem accomplishing this by SELECT TOP 5 from table A and SELECT TOP 15 from table B and using UNION but this only works if I have 5 or more patients in Category A. What I would really like to do is store a count of rows returned in my first query and then using SELECT TOP (20-n) from the second query but I am unsure of how to get this to work. Can anyone help with this syntax/logic? The below is not working because I'm not sure how to declare a variable and use a table alias AND use a union or if this is even possible. Thanks!

(SELECT TOP 5
        pata.patid "ID"
,       min(zcenc.NAME) "ENCOUNTER TYPE"


FROM TABLEA pata
    LEFT JOIN ENCOUNTER zcenc
        ON pata.pat_id = zcenc.pat_id
    
WHERE ......

GROUP BY ....) sqa

UNION

(declare @n as int
set n = count(*)
FROM sqa;


(SELECT TOP (20-n)
        patb.patid "ID"
,       min(zcenc.NAME) "ENCOUNTER TYPE"


FROM TABLEA patb
    LEFT JOIN ENCOUNTER zcenc
        ON patb.pat_id = zcenc.pat_id
    
WHERE ......

GROUP BY ....)
)

you can probably use rownumber(). Query the first table with a LIMIT 5, count the number of tuples in this result and then using rownumber() select tuples with a row number less or equal the size you need. It can be done in a single query. — dmg

Unknown Unknown · Accepted Answer · 2021-03-18T00:58:54

There are multiple ways to do this but I'll stick with what you're doing as you've asked.

Can anyone help with this syntax/logic?

Declare the parameter at the start and not in the middle of the union.

declare @n as int;

Select @n = count(*) FROM TABLEA pata;

Keep in mind that you're counting all of the rows in sqa and not just 0 - 5, that's fine if you have business logic that limits the potential rows in Category A to a maximum of 5. You can use @@ROWCOUNT or the equivalent to determine how many rows were actually affected by the previous statement. There are many different ways to do this and I'll add a case expression way at the end.

select Top 5 * FROM TABLEA pata;

set @n= @@ROWCOUNT;

From here you can select from Category B with Select top (20 - @n) * FROM TABLEA patb

So all up

declare @n as int;
select Top 5 * FROM TABLEA Pata;
set @n=  @@ROWCOUNT

select Top 5 * FROM TABLEA Pata
UNION 
select Top (20 -@n) * FROM TABLEA Patb

Without Row count with a case expression to set the variable.

declare @n as int;
select @n = Case WHEN count(*) >=5 then 5 Else count(*) END FROM TABLEA Pata
    
    select Top 5 * FROM TABLEA Pata
    UNION 
    select Top (20 -@n) * FROM TABLEA Patb

Count Rows in One Table and Use that Variable in Another (Union)

1 Answers