Order by and group by on result of UNION

Off-Topic
1

Hi! In SQL reference of MemSQL 5.5 it’s written that MemSQL does not support ORDER BY and GROUP BY on the result of a UNION. I.e. “(SELECT * FROM table_a UNION ALL SELECT * FROM table_b) GROUP BY x” is nto supported. However for 5.7 and upwords there is no more such warning. Question - does new MemSQL versions support order/group rules on UNION results? If yes - is there any special syntax, because I somehow cant get it to work. If it’s still not supported, why this note was removed from the docs?

2

To ORDER BY the result of a UNION or UNION ALL, first do the UNION/UNION ALL in a subquery. Then ORDER BY in the outer query, e.g.:

memsql> select r.a from (
    ->   select a from t
    ->   union
    ->   select a from s) as r
    -> order by r.a desc;
+------+
| a    |
+------+
|    3 |
|    2 |
|    1 |
+------+

Similarly, you could use a common table expression for this and then ORDER by in the final query.

memsql> with r(a) as (select a from t union select a from s) 
                select a from r order by a desc;
+------+
| a    |
+------+
|    3 |
|    2 |
|    1 |
+------+