Purpose
GROUP_ID() returns a unique identifier associated with a group of duplicate rows returned by the GROUP BY clause. It is commonly used in queries containing ROLLUP or CUBE operations to differentiate between different rows that produce the same grouping sets.
Limitations and considerations
GROUP_ID()cannot be used in theWHEREclause, join conditions, within aggregate functions, or in the definition of grouping attributes.The
GROUP_ID()function returns a number indicating the frequency of occurrence of the grouping set in the query result.When
GROUP_ID()is used, no parameters are required.
Syntax
GROUP_ID()
Examples
Create a table named t1 and insert multiple rows of data into the t1 table.
-- Create a table named `t1`.
CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 INT);
-- Insert data into the `t1` table.
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 by 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() function returns 0 and 1, respectively.
The return 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