GROUP_ID function

The GROUP_ID function uniquely identifies a group of rows that has been created by a GROUP BY clause in the query result set.

The GROUP BY extensions (such as CUBE) allow complex result sets that can include duplicate groupings. The GROUP_ID function allows you to distinguish among duplicate groupings.

If there are multiple sets of rows calculated for a given level, GROUP_ID assigns the value of 0 to all the rows in the first set. All other sets of duplicate rows for a particular grouping are assigned higher values, starting with 1.

GROUP_ID thus helps you filter out duplicate groupings from the result set. For example, you can filter out duplicate groupings by adding a HAVING clause condition GROUP_ID()=0 to the query.

GROUP_ID syntax

GROUP_ID cannot be used in a WHERE clause, join condition, inside an aggregate function, or in the definition of a grouping attribute.

The GROUP_ID syntax is:
GROUP_ID() AS alias

Note that the function does not accept any parameters.

GROUP_ID example

DEFINE r AS SELECT
  DimReseller_AnnualRevenue AS Revenue,
  DimReseller_AnnualSales AS Sales,
  DimReseller_OrderMonth AS OrderMonth;

RETURN results AS SELECT
  COUNT(1) AS COUNT,
  GROUP_ID() AS gid,
  GROUPING(Revenue) AS grouping_Revenue,
  GROUPING(Sales) AS grouping_Sales,
  GROUPING(OrderMonth) AS grouping_OrderMonth
FROM r
GROUP BY OrderMonth, ROLLUP(Revenue,Sales)