The GROUP_ID function distinguishes duplicate groups.

Description

If the query result includes duplicate groups, you can use the GROUP_ID function to distinguish these duplicate groups. The GROUP_ID function returns a unique ID of the INTEGER data type to distinguish duplicate groups.

If a specific group has n duplicates, the GROUP_IP function returns a number that ranges from 0 to n-1.

Note This function is applicable to the SELECT statements that include only the GROUP BY clauses.

Examples

The following table is used in this example.

 a | b | c
---+---+---
 1 | 2 | 3
  • If you use the group by rollup(a, b, c) clause to group data, the following four different groups are generated: a, b, c, (a, b), (a), and (). The following statement is executed:
    SELECT a, b, c, grouping(a, b, c), group_id() FROM t group by rollup(a, b, c) order by grouping(a, b, c);

    The following result is returned:

     a | b | c | grouping | group_id
---+---+---+----------+----------
 1 | 2 | 3 |        0 |        0
 1 | 2 |   |        1 |        0
 1 |   |   |        3 |        0
   |   |   |        7 |        0
(4 rows)
  • If you use the group by rollup(a, b, c), a, b clause to group data, four groups are generated. The four groups include three duplicate (a,b) groups. The following statement is executed:
    SELECT a, b, c, grouping(a, b, c), group_id() FROM t group by rollup(a, b, c), a, b order by grouping(a, b, c);

    The following result is returned:

     a | b | c | grouping | group_id
---+---+---+----------+----------
 1 | 2 | 3 |        0 |        0
 1 | 2 |   |        1 |        0
 1 | 2 |   |        1 |        1
 1 | 2 |   |        1 |        2
(4 rows)

If a group is generated for the first time, the value of GROUP_ID for this group is 0. If duplicates are generated for this group, the values of GROUP_ID for the duplicate groups are incremented from 1.