The Data Studio

Hive Cannot Order By An Aggregate Expression

Tested Using Hortonworks Data Platform (HDP) Sandbox, Release 2.5, Hive 2.1.0.2.5.0.0-1245

This qualifies as a minor annoyance because there is an easy workaround. If you have been using other databases, though, it will probably catch you out the first time, and when you forget.

      select
          postal_code,
          count(*)
      from
          customer 
      group by
          postal_code
      order by
          count(*);
    

This syntax works in most databases, but with Hive you get this error:

      Error: Error while compiling statement: FAILED: SemanticException [Error 10128]: Line 9:4 Not yet supported place for UDAF 'count' (state=42000,code=10128)
    

(UDAF stands for "User-Defined Aggregation Function" apparently - but count() isn't user-defined.)

The workaround is to give the count(*) an alias, like this:

      select
          postal_code,
          count(*) as frequency
      from
          customer 
      group by
          postal_code
      order by
          frequency;
    

Then it works fine. Every little hinders.

This issue is related to Hive, By Default, Does Not Allow Order By Result Column Number