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.