GROUP_BY_POSITION

2.147 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	`FALSE`
Modifiable	`ALTER SESSION`, `ALTER SYSTEM`
Modifiable in a PDB	Yes
Range of values	`TRUE \| FALSE`
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.