CODEWITHSUNDEEP
sqlsql-server
Tim
Tim

Reputation: 241

I would like to get the data 'order by' for each select query and then combine them using 'Union All'

I have two temp tables (#TempA, #TempB). each temp table has few records with the formorder 1, 2 and 3. Each Order has multiple records/rows.

For Example:

TempA

===========================
C1     C2    C3    FormOrder
===========================
abc    xyz   lmn    1
------------------------
anc    ppl   nmp    2  
----------------------
acc    bbl   mnp    3
-----------------------

Similarly, TempB has few records in the same format.

while combining these two Temp tables with using union all, I am getting the data. but the Data is not coming in order from the #TempA table.

I have tried adding 'Order by' at the end of the select query, but I am getting results in different way.

Without using Order By:

 select * from #TempA
    Union All
 select * from #TempB

Results:

===========================
C1     C2    C3    FormOrder
===========================
abc    xyz   lmn    1
-----------------------
acc    bbl   mnp    3
----------------------
anc    ppl   nmp    2
----------------------
xyz    ccc   nnn    1
------------------------
xyn    klm   uul    2
------------------------
cpp    klm   rnp    3
------------------------

Here the order from the #TempA is missing. So when I tried with Order By

select * from #TempA
    Union All
select * from #TempB
Order by FormOrder

Results:

==========================
C1     C2    C3    FormOrder
============================
abc    xyz   lmn    1
---------------------------
xyz    ccc   nnn    1
----------------------------
anc    ppl   nmp    2
----------------------------
xyn    klm   uul    2
--------------------------
acc    bbl   mnp    3
-----------------------------
cpp    klm   rnp    3
-------------------------

I am getting the above Order, but I am expecting the Order as 1,2,3 and them from TempA 1,2, 3 from TempB.

============================
C1     C2    C3    FormOrder
============================
abc    xyz   lmn    1
--------------------------
anc    ppl   nmp    2
-------------------------
acc    bbl   mnp    3
-----------------------
xyz    ccc   nnn    1
-------------------------
xyn    klm   uul    2
---------------------------
cpp    klm   rnp    3
------------------------

How can I achieve this? Any help much appreciated.

Upvotes: 0

Views: 88

Answers (3)

Rahid Zeynalov
Rahid Zeynalov

Reputation: 187

create table tempA(
    c1 varchar2(10),
    c2 varchar2(10),
    c3 varchar2(10),
    formOrder number(2)
    );

create table tempB(
    c1 varchar2(10),
    c2 varchar2(10),
    c3 varchar2(10),
    formOrder number(2)
    );

    insert into tempA values('abc','xyz','lmn',1);
    insert into tempA values('anc','ppc','nmp',2);
    insert into tempA values('acc','bbl','mnp',3);

    insert into tempB values('xyz','ccc','nnn',1);
    insert into tempB values('xyn','klm','nnl',2);
    insert into tempB values('cpp','klm','rnp',3);

    select * from tempA
    union all
    select * from tempB;


    C1         C2         C3          FORMORDER
    ---------- ---------- ---------- ----------
    abc        xyz        lmn                 1
    anc        ppc        nmp                 2
    acc        bbl        mnp                 3
    xyz        ccc        nnn                 1
    xyn        klm        nnl                 2
    cpp        klm        rnp                 3

Upvotes: -1

Sahi
Sahi

Reputation: 1484

you can try this:

    Declare @TempA Table(C1 varchar(5),C2 varchar(5),C3 varchar(5), FormOrder int)
    Declare @TempB Table(C1 varchar(5),C2 varchar(5),C3 varchar(5), FormOrder int)


    insert into @TempA
    SELECT 'abc','xyz','mn',1  Union All
    SELECT 'anc','ppl','mp',2  Union All  
    SELECT 'acc','bbl','np',3

    insert into @TempB
    SELECT 'xyz','ccc','nnn',1 Union All
    SELECT 'xyn','klm','uul',2 Union All
    SELECT 'cpp','klm','rnp',3

    ;with cte
    As
    (
        Select C1,C2,C3,FormOrder,NULL as FormOrder2 from @TempA 
        Union All
        Select C1,C2,C3,NULL as FormOrder, FormOrder as FormOrder2 from @TempB
    )

    Select C1,C2,C3,ISNULL(FormOrder,FormOrder2) As SortOrder 
    from cte order by FormOrder2,FormOrder

Upvotes: 0

HoneyBadger
HoneyBadger

Reputation: 15140

You can do the following:

SELECT  * 
FROM    (
            SELECT 'A' AS Source, * from #TempA
            Union All
            select 'B', * from #TempB
        ) SEL
Order by Source
,       FormOrder

Of course, you shouldn't use SELECT *, because one of the tables could change structure.

Upvotes: 2

Related Questions