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

Question

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.

Possible duplicate of [How to use order by with union all in sql?](https://stackoverflow.com/questions/15470191/how-to-use-order-by-with-union-all-in-sql) — Emka, Apr 17 '19 at 07:24
I think [this](https://stackoverflow.com/questions/24683766/how-to-use-order-by-inside-union?noredirect=1&lq=1) is the answer you're looking for. — MikeMapanare, Apr 17 '19 at 07:26
@MikeMapanare : I am getting this error message: "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified." — Tim, Apr 17 '19 at 07:50

score 2 · Answer 1 · answered Apr 17 '19 at 07:24

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.

score 0 · Answer 2 · answered Apr 17 '19 at 08:57

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

score -1 · Answer 3 · edited Apr 17 '19 at 09:21

-1

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

edited Apr 17 '19 at 09:21

Fourat

2,366
4
38
53

answered Apr 17 '19 at 07:45

Rahid Zeynalov

172
1
10

Please add further explanations to your answer. – Fourat Apr 17 '19 at 07:51
@Rahid: I am doing the same. but somehow, the order is missing. – Tim Apr 17 '19 at 07:54
If you don't have an `ORDER BY` applied to the whole `SELECT` statement that's doing retrieval, the ordering of results is not guaranteed. It's arbitrary (note, not the same thing as random). You have no `ORDER BY`, therefore this doesn't answer a problem requiring ordering. – Damien_The_Unbeliever Apr 17 '19 at 09:14

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

TempA

Without using Order By:

Results:

Results:

3 Answers3