The maximum value in a set of values can be determined using the MAX function. The syntax of MAX is shown below:

           ┌─ALL──────┐
>>──MAX──(─┼──────────┼─expression─)────────────────────────────────────────><
           └─DISTINCT─┘

The argument values can be of any built-in type other than a long string or DATALINK.

A SQLSTATE 42907 error will result if the result's data type is any of the following: LONG VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, DBCLOB, DATALINK, distinct type on any of these types, or structured type.

The argument and result values have the same data type, length and code page. The result can be null.

The function is applied to the set of values derived from the argument values by the elimination of null values.

The result is a null value if the function is applied to an empty set. Otherwise, the result is the maximum value in the set.

DISTINCT is only included for compatibility with other relational systems and should not be used.

Example:

Using the CONTRACTORS table, set the host variable MAX_RATE (decimal(7,2)) to the maximum quarterly contract rate (CONTRACT_RATE/4) value.

SELECT MAX(CONTRACT_RATE) / 4
  INTO :MAX_RATE
  FROM CONTRACTORS