SQL & how different databases implement it

Imagine you write a SELECT statement with a UNION or UNION ALL in it with each subselect having a GROUP BY clause, then add an ORDER BY clause to present the results set in a particular order. How do you construct the ORDER BY clause so that the data is presented in order of the group function or aggregate function?

Let me take this one step at a time.

Take a simple SELECT statement with a GROUP BY and an ORDER BY:

SELECT a_status, COUNT(*) AS cnt
FROM device
GROUP BY a_status
ORDER BY cnt

That is pretty straightforward. You could also write the ORDER BY as:
ORDER BY 2
or even:
ORDER BY COUNT(*)
and the statement will work as expected.

Now let's add a UNION ALL:

SELECT a_status, COUNT(*) AS cnt
FROM device
GROUP BY a_status
UNION ALL
SELECT b_status, COUNT(*) AS cnt_b
FROM device
GROUP BY b_status
ORDER BY cnt

This works fine. But if you change the ORDER BY clause in the previous statement so that it reads:
ORDER BY cnt_b
both Oracle and SQL Server will give error messages. In Oracle:
SQL Error: ORA-00904: "CNT_B": invalid identifier
In SQL Server:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'cnt_b'.
Msg 104, Level 16, State 1, Line 8
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Now change the ORDER BY clause to read:
ORDER BY COUNT(*)

In SQL Server, the statement will work just fine. In Oracle the statement will give an error message.
SQL Error: ORA-01785: ORDER BY item must be the number of a SELECT-list expression

Is this an important difference in behaviour? Almost certainly not. It has never been advisable or correct to specify an aggregate function in an ORDER BY clause. The developer should have given the aggregate function the same name in both select lists of the UNION (i.e. both COUNT(*)'s should have been called cnt). Or they should have specified the name of the select list item taken from the first part of the UNION. Or alternatively the numeric method of specifying an ORDER BY could have been used.