How to write Group by and Order by query with column position number in Hive

Hive query with Group by and order by

While we write the group by expression in Hive, we need to specify the column name instead of position number. If we specify the position number of the column in the group by expression, it will throw the error as “the position alias will be ignored“.

But we can resolve this issue by setting the following hive configuration property to true.

By default, the hive.groupby.orderby.position.alias property is set as false in Hive
0.11.0 through 2.2.0

Sample table

Example for position alias property

The table product_details contains the two columns such as product_id and name.Here we are grouping the products name and trying to get the count using the group by expression.

Instead of mentioning the group by column name, we have mentioned position number 1 in group by expression and got the below error.

Set the position alias property and run the query in Hive

Columns can be specified by position if hive.groupby.orderby.position.alias is set to true (the default is false). So setting set hive.groupby.orderby.position.alias=true;

Set hive.groupby.orderby.position.alias=true;
Output of group by and order by with position number

Recommended Articles