Purpose
The GROUP_ID function returns a unique identifier for a group that is repeated in the GROUP BY clause. It is commonly used in queries with ROLLUP or CUBE operations to distinguish between different rows that produce the same grouping set.
Limitations and considerations
The
GROUP_IDfunction cannot be used in theWHEREclause, join conditions, inside aggregate functions, or in the definition of grouping attributes.The
GROUP_IDfunction returns a number indicating how many times the grouping set appears in the query result.The
GROUP_IDfunction does not require any parameters.
Syntax
GROUP_ID()
Examples
A table named t1 is created, and multiple rows of data are inserted into it.
-- Create table t1
CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 INT);
-- Insert data into table t1
INSERT INTO t1 VALUES (1, 1, 1, 1);
INSERT INTO t1 VALUES (1, 1, 1, 2);
INSERT INTO t1 VALUES (1, 1, 2, 2);
INSERT INTO t1 VALUES (1, 2, 2, 2);
INSERT INTO t1 VALUES (2, 2, 2, 2);
-- Query and summarize data using GROUP BY with ROLLUP
SELECT c1, c2, c3, c4, GROUP_ID()
FROM t1 GROUP BY c1, ROLLUP(c1, c2, c3, c4) ORDER BY c1, c2, c3, c4;
The GROUP_ID() function is used to identify duplicate records returned by the GROUP BY clause. For example, for two identical summary records with c1=1, GROUP_ID() returns 0 and 1 respectively.
The result is as follows:
+------+------+------+------+------------+
| C1 | C2 | C3 | C4 | GROUP_ID() |
+------+------+------+------+------------+
| 1 | 1 | 1 | 1 | 0 |
| 1 | 1 | 1 | 2 | 0 |
| 1 | 1 | 1 | NULL | 0 |
| 1 | 1 | 2 | 2 | 0 |
| 1 | 1 | 2 | NULL | 0 |
| 1 | 1 | NULL | NULL | 0 |
| 1 | 2 | 2 | 2 | 0 |
| 1 | 2 | 2 | NULL | 0 |
| 1 | 2 | NULL | NULL | 0 |
| 1 | NULL | NULL | NULL | 0 |
| 1 | NULL | NULL | NULL | 1 |
| 2 | 2 | 2 | 2 | 0 |
| 2 | 2 | 2 | NULL | 0 |
| 2 | 2 | NULL | NULL | 0 |
| 2 | NULL | NULL | NULL | 0 |
| 2 | NULL | NULL | NULL | 1 |
+------+------+------+------+------------+
16 rows in set