SELECT TOP … FROM UNION
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!
Use the Top keyword for inner queries also:
SELECT TOP N ... FROM (SELECT TOP N... FROM Table1 UNION SELECT TOP N... FROM Table2)