May 16, 2013 at 9:14 am
John Mitchell-245523 (5/16/2013)
But that's because you changed the order. The proviso was that the UNION operator is the last one to be evaluated. If you use parentheses to make it so, you get the same results as before:
SELECT * FROM (VALUES (1), (1), (2)) x(A)UNION
(SELECT * FROM (VALUES (2), (3), (3)) y(B)
UNION all
SELECT * FROM (VALUES (4),(3)) z(C))
John
Edit - I think I understand what you're saying. I need to play around and see what happens when you have more than one UNION. I'll see if I get time to do that tomorrow.
Right but they made a blanket statement that you should NEVER us more than one union and it should always be the last one. That is just simply false. It depends on the requirements of the result set.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 17, 2013 at 1:20 am
Yes, I agree that it was worded too strongly. Can you think of any cases where it isn't possible to rewrite a series of UNIONs so that it contains only one UNION, with the rest being converted to UNION ALLs?
John
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply