The most efficient way to limit rows is returned from union query - TSQL

Question

Hi guys ... I have a simple stored procedure with two queries combined with a union

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

I want to limit this to TOP 10 results ...

if I put TOP 10 in every single request, I get only 20 results.

What is the most effective way to limit your overall results to 10? I don’t want to make TOP 5 in each, because I can be 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 

General table expression

Answer 2

 select top 10 * from ( select top 10 .... from .... where .... union select top 10 .... from .... where .... ) x 

- main idea. Adding the top 10 to each connection means that a smaller set will be installed in the external request.

If you want to set priority (i.e. return as much as possible from the first result), you can 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 much as possible from the first set and use only the results of the second set as a "backup".

Answer 3

Use the top for each subset and in the end use it for the merge 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 simply wrap this with a Sub Query or General table expression :

 ;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 easiest option is to just set the rowcount to 10

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