Oracle union all and order

// Sometimes, the result set and
// Merge other sorted result sets (union or union all)
// For example:
Select * from tb where length (id) = 5 order by id desc
Union all
Select * from tb where length (id) = 10 order by id asc
// In general, the preceding query will receive the following error message:
/ORA-00933: SQL command not properly ended
// Error pointing to union keyword here
// Let's look at a specific example:
//
Create table t
Select 'China' col_1, 'America 'col_2, 'Canada' col_3,-1 status from dual union all
Select 'Peanut ', 'melon seeds', 'mung bean ', 0 from dual union all
Select 'toothpaste ', 'toothbrush', 'cup ', 3 from dual union all
Select 'peony ', 'peony', 'Rose ', 1 from dual union all
Select 'youlemei', 'fragrance fluttering ', 'fried chicken', 2 from dual
/
// Requirement:
// As shown in the preceding table t, the value range of the status field is [-1, 3].
// What we want to do is sort 0, 1, 2, 3,-1 in this way.
//
// Solution:
// More specific. We need to divide status into two regions (> 0 and <0)
// Sort the data in each region by order
// The following query is available.
Select col_1, col_2, col_3, status
From t
Where status> = 0
Order by status -- 1
Union
Select col_1, col_2, col_3, status
From t
Where status <0
Order by status -- 2
/
// Unfortunately, as I prompted at the beginning, we get the following error message:
/ORA-00933: SQL command not properly ended
// If the order by clause of the first select statement is removed, the result is not what we want.
// If the two sort clauses are removed, although they are separated by positive and negative status, they are not sorted.
// Let's take a look at the correct answer!
// Solution 1:
Select * from (
Select col_1, col_2, col_3, status
From t
Where status> = 0
Order by status)
Union all
Select * from (
Select col_1, col_2, col_3, status
From t
Where status <0
Order by status)
/
COL_1 COL_2 COL_3 STATUS
-----------------------------
Peanut melon seeds mung bean 0
Peony rose 1
Youlemei fragrant fried chicken 2
Toothpaste, toothbrush, cup 3
China america canada-1
// Solution 2:
Select * from t
Order
Decode (status,
-1, 1,
3, 2,
2, 3,
1, 4,
0, 5) desc
/
// This is a wonderful sorting. I saw for the first time that the order by statement can be sorted using the decode () function.
// Similarly, we can also use the case statement for sorting:
// Solution 3:
Select * from t
Order
Case status
When-1 then 5
When 3 then 4
When 2 then 3
When 1 then 2
Else 1
End
/
// Both union and union all support order by and group by sorting and group clause