GROUP BY Extensions in Oracle 23c

Since a few days, Oracle Database 23c Free Developer Release is available. It contains many new features that are interesting for developers, especially some nice little extensions in SQL that we missed since years. Let’s start with an easy one in this blog post.

In previous versions of Oracle, I could never understand why a GROUP BY clause in a SELECT statement does not allow a column alias or a position number. For ORDER BY this is allowed, but not for GROUP BY. For example, if you run the following query on an Oracle 19c database, it will cause an error message:

 

SELECT TO_CHAR(bottling_date, ‘YYYY’) year

     , beer

     , beer_type

     , SUM(bottles)

  FROM v_beer_delivery

GROUP BY year, beer, beer_type

ORDER BY 1, 2, 3;

 

ORA-00904: “YEAR”: invalid identifier

 

The problem is the column alias year that is used in the SELECT clause. An alias is allowed in the ORDER BY clause, but not in the GROUP BY. There is no logical restriction for this – other relational databases support this syntax since years. In Oracle, we had to copy the expression of the SELECT clause into the GROUP BY clause to run the SQL statement:

 

SELECT TO_CHAR(bottling_date, ‘YYYY’) year

     , beer

     , beer_type

     , SUM(bottles)

  FROM v_beer_delivery

GROUP BY TO_CHAR(bottling_date, ‘YYYY’), beer, beer_type

ORDER BY 1, 2, 3;

 

 

GROUP BY Column Alias or Position in Oracle 23c

With Oracle 23c, this restriction does not exist anymore. Now, it is possible to use a column alias or the column position in a GROUP BY – in the same way as we know it from the ORDER BY clause. This was one of the first features I tested on the new 23c release, and it worked as expected for the column alias. The example query from the beginning of this blog post worked as expected.

But for the positional notation, I got another error message:

 

SELECT TO_CHAR(bottling_date, ‘YYYY’) year

     , beer

     , beer_type

     , SUM(bottles)

  FROM v_beer_delivery

GROUP BY 1, 2, 3

ORDER BY 1, 2, 3;

 

ORA-00979: “BOTTLING_DATE”: must appear in the GROUP BY clause or be used in an aggregate function

 

To avoid this error, the parameter group_by_position_enabled must be set to TRUE (it is FALSE by default).

ALTER SESSION SET group_by_position_enabled=TRUE;

 

After that, you can use column names, column aliases or positions in the GROUP BY part of your queries. This is a nice little extension, but it can be useful to reduce the complexity of complex queries.

This is just one of many new features that were introduced with Oracle 23c. If you didn’t do yet, download and install the Oracle Database 23c Free – Developer Release. There you can play around with many other extensions of the new release. An overview of all new features you can find in the Oracle Database New Features manual of the Oracle 23c documentation.