Using Select Numbers

You can use one or more integers in the GROUP BY clause to stand for column expressions. In the next example, the first SELECT statement uses select numbers for order_date and paid_date - order_date in the GROUP BY clause. You can group only by a combined expression using the select numbers.

In the second SELECT statement, you cannot replace the 2 with the arithmetic expression paid_date - order_date:

HAVING Clause

Use the HAVING clause to apply one or more qualifying conditions to groups.

HAVING Clause:
 
                         (1)
|--HAVING--| Condition |----------------------------------------|

Notes:

See Condition

In the following examples, each condition compares one calculated property of the group with another calculated property of the group or with a constant. The first SELECT statement uses a HAVING clause that compares the calculated expression COUNT(*) with the constant 2. The query returns the average total price per item on all orders that have more than two items.

The second SELECT statement lists customers and the call months for customers who have made two or more calls in the same month:

SELECT order_num, AVG(total_price) FROM items
   GROUP BY order_num HAVING COUNT(*) > 2 
SELECT customer_num, EXTEND (call_dtime, MONTH TO MONTH) 
   FROM cust_calls GROUP BY 1, 2 HAVING COUNT(*) > 1

You can use the HAVING clause to place conditions on the GROUP BY column values as well as on calculated values. This example returns cust_code and customer_num, call_dtime, and groups them by call_code for all calls that have been received from customers with customer_num less than 120:

SELECT customer_num, EXTEND (call_dtime), call_code 
   FROM cust_calls GROUP BY call_code, 2, 1
   HAVING customer_num < 120

The HAVING clause generally complements a GROUP BY clause. If you omit the GROUP BY clause, the HAVING clause applies to all rows that satisfy the query, and all rows in the table make up a single group. The following example returns the average price of all the values in the table, as long as more than ten rows are in the table:

SELECT AVG(total_price) FROM items HAVING COUNT(*) > 10

ORDER BY Clause

The ORDER BY clause sorts query results by specified columns or expressions.

ORDER BY Clause:
 
|--ORDER BY----------------------------------------------------->
 
   .-,----------------------------------------------------------------------------------.
   V                                                                       .-ASC------. |
>----+-+-+----------------------+--column-+--+------------------------+-+--+----------+-+--|
     | | |                 (1)  |         |  |  (3)                   | |  '-+------+-'
     | | '-| Data Source |------'         |  '--------[ first, last ]-' |    '-DESC-'
     | |                (2)               |                             |
     | '-| Expression |-------------------'                             |
     '-+-select_number-------+------------------------------------------'
       +-display_label-------+
       |  (3)    (4)         |
       '---------------ROWID-'

Notes:

See WHERE Clause
See Expression
Informix extension
Dynamic Server only

Element	Description	Restrictions	Syntax
column	Sort rows by value in this column	Must also be in select list (Extended Parallel Server only).	Identifier
display_label	Temporary name for a column or for a column expression	Must be unique among labels declared in the Projection clause	Identifier
first, last	First and last byte in column substring to sort the result set	Integers; for BYTE, TEXT, and character data types only	Literal Number
select_ number	Ordinal position of a column in select list of the Projection clause	See Using Select Numbers.	Literal Number

The ORDER BY clause is not valid in queries within an SPL routine.