InterBase Quick Start: Part IV - Ordering by an Aggregate Column
Go Up to InterBase Quick Start: Part IV - Grouping and Ordering Query Results
But what if you want to list the result set by the average budget in the previous query? In that query, it would not work to say “ORDER BY budget” because the query generates a two-column result set in which the first column is named “department” but the second column, which is generated by the aggregate function, does not have a name. The ORDER BY clause is actually referencing the columns of the result set. In order to request that the result set be ordered by the results of an aggregate function, you must reference the ordinal column number. Look back at the ORDER BY syntax (Using ORDER BY to Arrange Rows) and notice that it begins “ORDER BY [col_name | int]“. You must reference the column by its integer number.
Ordering Result Set by the Results of an Aggregate Function
Display your last query, but change the ORDER BY clause as follows, to order by the second column:
SELECT department, AVG(budget) FROM department
GROUP BY Department
HAVING AVG(budget) > 60000
ORDER BY 2
Now the result set should have the second column in ascending order.
