www.mimer.com/developer

Selecting Groups - the HAVING Clause

---

The HAVING clause restricts the selection of groups in the same way that a WHERE clause restricts the selection of rows. However, in contrast to the WHERE clause, a HAVING clause may use a set function on the left-hand side of a comparison.

The HAVING clause is most often used together with a GROUP BY clause, but may also be used to impose selection conditions on a column derived from a set function.

Find the highest price for a SMOKING single room in each hotel, but restrict the selection to prices over 1000.

 SELECT  HOTELCODE, MAX(PRICE)
 FROM    ROOM_PRICES
 WHERE   ROOMTYPE = 'SSGLB'
 OR      ROOMTYPE = 'SSGLS'
 GROUP BY HOTELCODE 
 HAVING  MAX(PRICE) > 1000;

Result:

HOTELCODE
WIND	1410
WINS	1370

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se