BigQuery: Best way to ORDER BY an arbitrary GROUP BY expression when that expression is not in the SELECT clause?

In the below query, I want to group all orders by the concatenation of firstname and lastname, and order the result set by that concatenation. I do not want to include the concatenation in the SELECT column list. BigQuery returns the following error for the query:

SELECT 
    COUNT(o.ORDERID)
FROM EMPLOYEES e
INNER JOIN ORDERS o ON e.EMPLOYEEID = o.EMPLOYEEID
GROUP BY CONCAT(e.FIRSTNAME, ' ', e.LASTNAME)
ORDER BY CONCAT(e.FIRSTNAME, ' ', e.LASTNAME)

ORDER BY clause expression references column [column_name] which is neither grouped nor aggregated

It seems to me that the firstname/lastname concatenation is necessarily unique per group, so one should be able to include it in the ORDER BY clause.

I can fix the error by wrapping the ORDER BY clause in ANY_VALUE:

SELECT 
    COUNT(o.ORDERID)
FROM EMPLOYEES e
INNER JOIN ORDERS o ON e.EMPLOYEEID = o.EMPLOYEEID
GROUP BY CONCAT(e.FIRSTNAME, ' ', e.LASTNAME)
ORDER BY ANY_VALUE(CONCAT(e.FIRSTNAME, ' ', e.LASTNAME))

Is this best practice? Is there a more canonical way to do this for arbitrary GROUP BY expressions? Other DBMS, including MySQL, SQLServer, Postgres, etc. handle the first query without error.

Answer

Your method is actually fine, although I usually use MIN() or MAX() just because that is common across all SQL dialects.

However, here is a trick for defining new column aliases in the FROM clause, which then allows you to use them in the GROUP BY and ORDER BY:

SELECT COUNT(o.ORDERID)
FROM EMPLOYEES e INNER JOIN
     ORDERS o
     ON e.EMPLOYEEID = o.EMPLOYEEID CROSS JOIN
     UNNEST(ARRAY[CONCAT(e.FIRSTNAME, ' ', e.LASTNAME)]) fullname
GROUP BY fullname
ORDER BY fullname;

You could also use a correlated subquery:

select (select count(*)
        from orders o
        where o.employeeid = e.employeeid
       )
from employees e
order by CONCAT(e.FIRSTNAME, ' ', e.LASTNAME)

Note: This returns 0 values as well.