GROUP_ID() assigns a sequence number to each duplicate group produced by GROUP BY extensions such as ROLLUP, CUBE, and GROUPING SETS. Use it to identify and filter out duplicate groupings in complex aggregation queries.
Description
GROUP BY extensions can generate duplicate groupings in a single result set. GROUP_ID() returns an INTEGER that numbers each occurrence of a duplicate group, starting at 0. If a group appears *n* times, its occurrences receive values 0 through n-1.
GROUP_ID() applies only to SELECT statements that contain a GROUP BY clause.
| Occurrence | GROUP_ID value |
|---|---|
| First | 0 |
| Second | 1 |
| Third | 2 |
| ... | ... |
Examples
The examples use the following table:
a | b | c
---+---+---
1 | 2 | 3No duplicate groups
GROUP BY ROLLUP(a, b, c) generates four distinct groups: (a, b, c), (a, b), (a), and (). No duplicates exist, so GROUP_ID() is 0 for all rows.
SELECT a, b, c, grouping(a, b, c), group_id()
FROM t
GROUP BY ROLLUP(a, b, c)
ORDER BY grouping(a, b, c);Result:
a | b | c | grouping | group_id
---+---+---+----------+----------
1 | 2 | 3 | 0 | 0
1 | 2 | | 1 | 0
1 | | | 3 | 0
| | | 7 | 0
(4 rows)Duplicate groups
GROUP BY ROLLUP(a, b, c), a, b produces the same four rows, but adding a, b to the GROUP BY clause causes the (a, b) group to appear three times. GROUP_ID() numbers the duplicates 0, 1, and 2.
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);Result:
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)