2.148 GROUP_BY_POSITION_ENABLED
GROUP_BY_POSITION_ENABLED
controls whether a positive integer in a GROUP
BY
clause is treated as the ordinal position of an expression in the select list.
Property | Description |
---|---|
Parameter type |
Boolean |
Default value |
|
Modifiable |
|
Modifiable in a PDB |
Yes |
Range of values |
|
Basic |
No |
Oracle RAC |
The same value must be used on all instances. |
In SQL SELECT
statements, you can specify the GROUP
BY
clause when you want to group the selected rows that share a common value for an expression and return a single row of summary information for each group.
In previous releases, if you wanted to group rows according to the value of an expression in the select list, you specified the expression as it appeared in the select list. For example:
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;
Starting with Oracle Database 23ai, when GROUP_BY_POSITION_ENABLED
is set to TRUE
, you can specify GROUP
BY
position
, where position
is a positive integer that represents the ordinal position of an expression in the select list. For example, the following SELECT
statement groups the selected rows by the value of department_id
:
SELECT department_id, COUNT(*)
FROM employees
GROUP BY 1;
The following SELECT
statement groups the selected rows, first by the value of manager_id
, and then by the value of hired_year
:
SELECT manager_id, EXTRACT(YEAR FROM hire_date) AS hired_year, COUNT(*)
FROM employees
GROUP BY 1, 2;
Note:
This parameter is available starting with Oracle Database 23ai.
See Also:
Oracle Database SQL
Language Reference for more information about the GROUP
BY
clause of the SELECT
statement