InterBase Quick Start: Part IV - Ordering by an Aggregate Column

Go Up to InterBase Quick Start: Part IV - Grouping and Ordering Query Results

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.

Ordering Result Set by the Results of an Aggregate Function

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:

Advance To:

• Joining Tables