Columns, Group Functions, and ORDER BY in Oracle 12c

By: Bob Bryla, Kevin Loney


The ORDER BY clause is executed after the WHERE, GROUP BY, and HAVING clauses. It can employ group functions, or columns from the GROUP BY, or a combination. If it uses a group function, that function operates on the groups and then the ORDER BY sorts the results of the function in order. If the ORDER BY uses a column from the GROUP BY, it sorts the rows that are returned based on that column. Group functions and single columns (so long as the column is in the GROUP BY) can be combined in the ORDER BY.

In the ORDER BY clause, you can specify a group function and the column it affects even though they have nothing at all to do with the group functions or columns in the SELECT, GROUP BY, or HAVING clause. On the other hand, if you specify a column in the ORDER BY clause that is not part of a group function, it must be in the GROUP BY clause. Let’s take the last example and modify the ORDER BY clause:

p0238-01

The titles and authors will now be ordered based on the number of authors (with the greatest number first) and then by TITLE and AUTHOR_NAME:

p0238-02

Leave a Reply