asked
SQL Union Operator
Hi, Here is the example query. Query1: Select supp_addr_addid 'billtoaddid' from supp_addr_address(nolock), supp_bu_suplmain (nolock) where supp_addr_supcode = supp_bu_supcode and supp_addr_supcode = '12069' and supp_addr_addid = supp_bu_deforderto billtoaddid ----------- 2 (1 row(s) affected) Query 2: Select supp_addr_addid 'billtoaddid' from supp_addr_address(nolock), supp_bu_suplmain (nolock) where supp_addr_supcode = '12069' and supp_addr_supcode = supp_bu_supcode and supp_addr_loid = 'MJBLO' and supp_addr_supcode in ( select supp_ou_supcode from supp_ou_suplmain (nolock) where supp_ou_ouinstid = 3 and supp_ou_supcode = '12069' ) Query 2 output: billtoaddid ----------- 1 2 (2 row(s) affected) I Need to write union for the query 1 and query 2 but the result set of the query 1 should come first. If i write the union query as below, Select supp_addr_addid 'billtoaddid' from supp_addr_address(nolock), supp_bu_suplmain (nolock) where supp_addr_supcode = supp_bu_supcode and supp_addr_supcode = '12069' and supp_addr_addid = supp_bu_deforderto union Select supp_addr_addid 'billtoaddid' from supp_addr_address(nolock), supp_bu_suplmain (nolock) where supp_addr_supcode = '12069' and supp_addr_supcode = supp_bu_supcode and supp_addr_loid = 'MJBLO' and supp_addr_supcode in ( select supp_ou_supcode from supp_ou_suplmain (nolock) where supp_ou_ouinstid = 3 and supp_ou_supcode = '12069' ) The output comes as below. billtoaddid ----------- 1 2 (2 row(s) affected) I need a output of query 1 to come first. Please help any question let me know. Thanks Satish
Satish - can you post the answer you found, it will help others who find your question useful
It looks like there are two things preventing you from getting the output you're after. - The only way to guarantee the order of a query is with an ORDER BY clause. - Using UNION instead of UNION ALL only gives you a distinct list, not ALL the rows. Currently your final output is 1 2 You're wanting to get 2 (from Q1) 1 (from Q2) 2 (from Q2) ...in that order. To achieve this you're going to need something like SELECT blah ,1 as sort_order FROM #q1 UNION ALL SELECT blah ,2 as sort_order FROM #q2 ORDER BY sort_order If you don't want the sort column coming through in the final output, this might be a good option: WITH your_output as ( SELECT blah ,1 as sort_order FROM q1 UNION ALL SELECT blah ,2 as sort_order FROM q2 ) SELECT blah FROM your_output ORDER BY sort_order In this instance you'll get the right answer using UNION instead of UNION ALL, because the sort_order column we added differentiates between the output from q1 and q2, so why did I bother? Because UNION ALL is much cheaper than UNION. In order to get the distinct list that UNION outputs it has to sort the data to find the duplicates. This extra work comes at a price, especially in long lists.
9 People are following this question.
