Purpose
GROUP_ID returns a unique identifier associated with the duplicate groups in the GROUP BY clause. It is commonly used in queries containing ROLLUP or CUBE operations to differentiate between different rows producing the same grouping sets.
Considerations
GROUP_IDcannot be used in theWHEREclause, join conditions, within aggregate functions, or in the definition of grouping attributes.The
GROUP_IDfunction returns a number indicating the frequency of occurrence of the grouping set in the query result.When using
GROUP_ID, no parameters are required.
Syntax
GROUP_ID()
Examples
Create a table named t1 and insert multiple rows of data into table t1.
-- 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 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 identical summary records with c1=1, the GROUP_ID() values are 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