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;