SELECT TOP ... FROM UNION

Question

What is the best way to SELECT TOP N records from UNION of 2 queries?

I can't do

SELECT TOP N ... FROM
   (SELECT ... FROM Table1
    UNION
    SELECT ... FROM Table2)

because both queries return huge results I need every bit of optimization possible and would like to avoid returning everything. For the same reason I cannot insert results into #TEMP table first either.

I can't use SET ROWCOUNT N either because I may need to group results and this command will limit number of grouped rows, and not underlying row selections.

Any other ideas? Thanks!

Solution

Use the Top keyword for inner queries also:

SELECT TOP N ... FROM
(SELECT TOP N... FROM Table1
UNION
SELECT TOP N... FROM Table2) as result