Yuriy Galanter Yuriy Galanter -4 years ago 110
SQL Question

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!

Answer Source

Use the Top keyword for inner queries also:

SELECT TOP N ... FROM
(SELECT TOP N... FROM Table1
UNION
SELECT TOP N... FROM Table2)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download