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

From InterBase
Jump to: navigation, search

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.

Image 025.jpgOrdering 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:

TutorialGrouping5.png

Advance To: