Oracle union with 2 selects and order by in individual select and order by in main select-CodePudding

I have 2 queries with 2 different condition but same return columns with a union. I did an outer order by with the main select and everything works. What should I do to do an orderBy in upper select and different orderby in the lower select with union.

This is my query:

select * from (
select c.a as column1,c.b as column2 ,c.c as column3 , c.caseType caseType, 1 as ordered
from cases c where
1=1 and c.caseType = 'P'
union
select c.a as column1,c.b as column2 ,c.c as column3 , c.caseType caseType, 2 as ordered 
from cases c where
1=1 and caseType = 'M'
) order by ordered asc



case Table
a    b    c    caseType
1    A    10    P
2    B    11    P   
3    C    12    M
4    D    13    M
5    E    14    P

My output looks like this:

column1 column2 column3 caseType ordered
1       A       10      P           1
2       B       11      P           1
5       E       14      P           1
3       C       12      M           2
4       D       13      M           2

I want a query like this to get the below output:If caseType is P I should order by column1 as in the 1st select and if caseType is M then I should do a order by column3 as in the 2nd select.

select * from (
select c.a as column1,c.b as column2 ,c.c as column3 , c.caseType caseType, 1 as ordered
from cases c where
1=1 and c.caseType = 'P'
order by column1 asc
union
select c.a as column1,c.b as column2 ,c.c as column3 , c.caseType caseType, 2 as ordered 
from cases c where
1=1 and caseType = 'M'
order by column3 desc
) order by ordered asc

Output should look like this:

column1 column2 column3 caseType ordered
1       A       10      P           1
2       B       11      P           1
5       E       14      P           1
4       D       13      M           2
3       C       12      M           2

Suggestions please? Also order by is dynamic, each select might or may not have order by clause but the outer order by is static. Some cases the upper select maynot have order by and vise versa. Sometimes both might have and both might not have too.

CodePudding user response：

I'm not sure I fully understand the question but you can get the result you need by unioning CTE or subqueries with the sort applied within the CTE/subquery. In the example you have, your outer order by isn't really doing any useful work, though maybe in your real world problem it is. At moment, I'm ignoring it since I can't sort what it is adding other than to sort the union, which is already sorted. I am similarly ignoring the where 1=1 condition as that is adding nothing.

Rewriting your query (and not checking for any other errors):

with
p_table as (
    select 
    c.a as column1,
    c.b as column2,
    c.c as column3, 
    c.caseType caseType, 
    1 as ordered

    from cases c 

    where c.caseType = 'P'

    order by column1 asc
), 

m_table as (
    select 
    c.a as column1,
    c.b as column2,
    c.c as column3, 
    c.caseType caseType, 
    2 as ordered 

    from cases c 
    where caseType = 'M'

    order by column3 desc
)

select 
*

from p_table

union all
select 
* 

from m_table