HAVING clause

<< GROUP BY clause | ColumnPosition >>

Derby Reference Manual

GROUP BY ROLLUP(Region, State, Product)

HAVING clause

A HAVING clause restricts the results of a GROUP BY in a

SelectExpression

. The

HAVING clause is applied to each group of the grouped table, much as a WHERE clause
is applied to a select list. If there is no GROUP BY clause, the HAVING clause is applied
to the entire result as a single group. The SELECT clause cannot refer directly to any
column that does not have a GROUP BY clause. It can, however, refer to constants,
aggregates, and special registers.

Syntax

HAVING searchCondition

The searchCondition, which is a specialized booleanExpression, can contain only
grouping columns (see

GROUP BY clause

), columns that are part of aggregate

expressions, and columns that are part of a subquery. For example, the following query is
illegal, because the column SALARY is not a grouping column, it does not appear within
an aggregate, and it is not within a subquery:

-- SELECT COUNT(*)
-- FROM SAMP.STAFF
-- GROUP BY ID
-- HAVING SALARY > 15000

Aggregates in the HAVING clause do not need to appear in the SELECT list. If the
HAVING clause contains a subquery, the subquery can refer to the outer query block if
and only if it refers to a grouping column.

Example

-- Find the total number of economy seats taken on a flight,
-- grouped by airline,
-- only when the group has at least 2 records.
SELECT SUM(ECONOMY_SEATS_TAKEN), AIRLINE_FULL
FROM FLIGHTAVAILABILITY, AIRLINES
WHERE SUBSTR(FLIGHTAVAILABILITY.FLIGHT_ID, 1, 2) = AIRLINE
GROUP BY AIRLINE_FULL
HAVING COUNT(*) > 1

ORDER BY clause

The ORDER BY clause is an optional element of the following:

· A

· A

· A

· A

· A

It can also be used in an

INSERT statement

or a

CREATE VIEW statement

An ORDER BY clause allows you to specify the order in which rows appear in the result
set. In subqueries, the ORDER BY clause is meaningless unless it is accompanied
by one or both of the

result offset and fetch first clauses

or in conjunction with the

ROW_NUMBER function

, since there is no guarantee that the order is retained in the

outer result set. It is permissible to combine ORDER BY on the outer query with ORDER
BY in subqueries.

Syntax

ORDER BY {

column-Name

| ColumnPosition | Expression }