Ordering by an Aggregate Column

But what if you want to list the result set by the average budget in the previous query? In that query, it wouldn’t 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, doesn’t 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 on page 20 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.

Advance To:

Joining Tables