Solve the problem of sorting after union all merges two result sets

In daily development, if the utility union all merges two ordered result sets, the requirement is that the second result set data is ranked below the first result set data, and the simple utility order by is invalid because the order by has a lower precedence than union all.

For example:

Select one.* from (select t1.* from table1 t1 where 1=1 and T1.day >3 ORDER by t1.create_date Desc) One

UNION All

Select Two.* from (select t2.* from Table2 T2 where 1=1 and t1.day <=3 ORDER by t2.create_date Desc)

At this point the query results are not OK two sorted after the one, if you want to achieve such a requirement, you can use a temporary field as a sort field, the purpose of this field is to make the merged result set has a uniform sort field, the result set before the merge to the default values of 1 and 2, This allows the result set to be sorted after the merge.

Select All.* from (select one.* from (select t1.*,' 1 "as ' SortBy ' from table1 t1 where 1=1 and T1.day >3 Orde R by t1.create_date Desc) One

UNION All

Select Two.* from (select t2.*,' 2 ' as ' SortBy ' from table2 T2 where 1=1 and t.2day <=3 ORDER by t2.create _date desc), all where 1=1 order by all.sortby ASC

The clever thing about the solution is to use a custom field to implement the combined ordering of the two result sets.

Solve the problem of sorting after union all merges two result sets