InterBase Quick Start: Part IV - Ordering by an Aggregate Column
If you want to list the result set by the average budget in the previous query, you can not do that by specifying
ORDER BY budget, because the query generates a two-column result set in which the first column is named
department and the second colum does not have a name. The
ORDER BY clause references the columns of the result set, so in order to specify the order of a culumn that is a result of an aggregate function, you must reference the ordinal column number.
Modify the previous query to order by the second column:
SELECT department, AVG(budget) FROM Department GROUP BY department HAVING AVG(budget) > 60000 ORDER BY 2
The result set shows the second column in ascending order: