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

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.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source