Menu
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!
772
The UNION operator is used to combine the data from the result of two or more SELECT command queries into a single distinct result set. This operator removes any duplicates present in the results being combined.
The SQL SELECT TOP statement is used to retrieve records from one or more tables in a database and limit the number of records returned based on a fixed value or percentage. TIP: SELECT TOP is Microsoft's proprietary version to limit your results and can be used in databases such as SQL Server and MSAccess.
We can use TOP Clause in a SQL delete statement as well. We should use the ORDER BY clause in the delete statement to avoid unnecessary deletion of data. In the above query, we want to retrieve the top 10 customers' records in [orderdate] ascending order.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
