Purpose
The GROUP_ID function returns a unique identifier for a group of duplicate rows specified by the GROUP BY clause. It is typically used in queries with ROLLUP or CUBE operations to differentiate between 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 the data using GROUP BY and 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 rows with the same aggregation result where 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