CODEWITHSUNDEEP
mysqlsqlunion
BrTkCa
BrTkCa

Reputation: 4783

Merge results in union all sql

Table A:

Column1 | Column2
tableA01 | tableA11
tableA02 | tableA22

Table B:

Column1 | Column2
tableB01 | tableB11
tableB02 | tableB22

When sql using union all in these tables, the results like this:

SQL

SELECT * FROM tableA UNION ALL SELECT * FROM tableB

Result

Column1  | Column2
tableA01 | tableA11
tableA02 | tableA22
tableB01 | tableB11
tableB02 | tableB22

I ask: It possible merge the results?

I want the results somelike this:

Column1  | Column2
tableA01 | tableA11
tableB01 | tableB11
tableA02 | tableA22    
tableB02 | tableB22

Thanks!

Upvotes: 1

Views: 84

Answers (2)

openwonk
openwonk

Reputation: 15567

Simply order your unified results.

SELECT *
FROM (
    SELECT Column1, Column2 FROM tableA
    UNION ALL 
    SELECT Column1, Column2 FROM tableB
) Results 
ORDER BY Column1, Column2

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269783

Your result sets are the same, because result sets are unordered, unless you have an order by. Because your sample query doesn't have an order by, the two are equivalent.

More generally, though, you seem to want to interleave the values. You can do this using order by by doing:

select ab.*
from ((select a.*, (@rna := @rna + 1) as rn, 1 as which
       from a cross join (select @rna := 0) params
       order by <something>  -- You should order by something here so the ordering is well defined
      ) union all
      (select b.*, (@rnb := @rnb + 1) as rn, 2 as which
       from b cross join (select @rnb := 0) params
       order by <something>  -- You should order by something here so the ordering is well defined
      )
     ) ab
order by rn, which;

Upvotes: 1

Related Questions