CODEWITHSUNDEEP
sqlsql-serverselectunion
suff trek
suff trek

Reputation: 39777

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!

Upvotes: 11

Views: 11972

Answers (2)

Deepak.Aggrawal
Deepak.Aggrawal

Reputation: 1277

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

Upvotes: 19

user2343475
user2343475

Reputation:

You could try the following. It uses a CTE which would give you the results of both queries first and then you would select your TOP N from the CTE.

WITH table_cte (
(
    [col1],
    [col2],
    ...
)
AS
(
    SELECT *
    FROM table1
    UNION ALL 
    SELECT *
    FROM table2
)

SELECT TOP 1000 * FROM table_cte

Upvotes: 0

Related Questions