Most efficent way to limit rows returns from union query- TSQL

Question

I have a simple stored proc with two queries joined with a union:

select name as 'result'
from product
where...

union

select productNum as 'result'
from product
where...

I want to limit this to the TOP 10 results.

If I put TOP 10 in each seperate query I get 20 results total.

What is the most efficient way to limit total results to 10? I dont want to do TOP 5 in each because I may end up in a situation where I have something like 7 "names" and 3 "productsNumbers".

Answer 1

WITH Results (Result)
AS
(

select name as 'result'
from product
where...

union

select productNum as 'result'
from product
where...
)

SELECT TOP 10 * FROM Results

Common Table Expression

Answer 2

select top 10 * from
(
select top 10 ....
from ....
where ....

union

select top 10 ....
from ....
where ....
) x

is the basic idea. Adding the top 10 to each union means you will have a smaller set to limit in the outer query.

If you want to prioritise (i.e. return as many as possible from first result) then you could do this:

select top 10 * from
(
select top 10 
1 as prio_col, ....
from ....
where ....

union

select top 10 
2 as prio_col....
from ....
where ....
) x
order by prio_col

so that you get as many as possible from the first set, and only use results from the second set as a "fallback".

Answer 3

Use the top for each sub set and at the end use it for union result.

select top 10 * from (
select top 10 name as 'result'
from product
where...

union

select top 10 productNum as 'result'
from product
where...
)

Answer 4

You can just wrap this with a Sub Query or Common Table Expression like this:

;with cte as 
(select name as 'result'
from product
where...

union

select productNum as 'result'
from product
where...) 
select top 10 * from cte;

Answer 5

The simplest option is just to Set the Rowcount to 10

Set RowCount 10
select name as 'result'
from product
where...

union

select productNum as 'result'
from product
where...