GROUP_ID
The GROUP_ID
function identifies duplicate groups in a SELECT
query resulting from a GROUP
BY
clause. This function returns the number 0 for a unique group; any subsequent duplicate grouping row receives a higher number, starting with 1. The GROUP_ID
function filters out duplicate groupings from the query result. If you have complicated queries that may generate duplicate values, you can eliminate those rows by including the HAVING GROUP_ID() = 0
condition.
Note:
See "GROUP BY Clause" for details on the GROUP BY
clause. See "SELECT" for details on the HAVING
clause.
Syntax
The GROUP_ID
function is applicable only in a SELECT
statement that contains a GROUP BY
clause; it can be used in the select list and HAVING
clause of the SELECT
query.
GROUP_ID()
Parameters
GROUP_ID
has no parameters.
Example
The following example shows how GROUP_ID
returns 0 for a unique group and a number > 0 to identify duplicate groups. The following example prints out the department number, manager id and the sum of the salary within the manager. The resulting output is grouped using the ROLLUP
clause on the manager and department providing superaggregate results.
Command> SELECT department_id as DEPT, manager_id AS MGR, GROUP_ID(), SUM(salary) as SALARY FROM employees WHERE manager_id > 146 GROUP BY manager_id, ROLLUP(manager_id, department_id) ORDER BY manager_id, department_id; DEPT, MGR, EXP, SALARY < 80, 147, 0, 46600 > < <NULL>, 147, 1, 46600 > < <NULL>, 147, 0, 46600 > < 80, 148, 0, 51900 > < <NULL>, 148, 0, 51900 > < <NULL>, 148, 1, 51900 > < 80, 149, 0, 43000 > < <NULL>, 149, 0, 7000 > < <NULL>, 149, 0, 50000 > < <NULL>, 149, 1, 50000 > < 20, 201, 0, 6000 > < <NULL>, 201, 0, 6000 > < <NULL>, 201, 1, 6000 > < 110, 205, 0, 8300 > < <NULL>, 205, 0, 8300 > < <NULL>, 205, 1, 8300 > 16 rows found.