The MAX function returns the maximum value in a set of values.

The schema is SYSIBM.

expression

An expression that returns a value of any built-in data type other than BLOB, CLOB, DBCLOB, ROWID, or XML.

The data type, length and code page of the result are the same as the data type, length and code page of the argument values.
The result is considered to be a derived value and can be null.

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

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

The specification of DISTINCT has no effect on the result and therefore is not recommended.
It is included for compatibility with other relational systems.

Notes

• Results involving DECFLOAT special values:
  If the data type of the argument is decimal floating-point and positive or negative infinity, sNaN, or NaN is found, the maximum value is determined using decimal floating-point ordering rules.
  If multiple representations of the same decimal floating-point value are found (for example, 2.00 and 2.0), it is unpredictable which representation will be returned.

Examples

1. Using the EMPLOYEE table, set the host variable MAX_SALARY (decimal(7,2)) to the maximum monthly salary (SALARY/12) value.

      SELECT MAX(SALARY) / 12
      INTO  :MAX_SALARY
      FROM   EMPLOYEE

   Results in MAX_SALARY being set to 4395.83 when using the sample table.


2. Using the PROJECT table, set the host variable LAST_PROJ(char(24)) to the project name (PROJNAME) that comes last in the collating sequence.

      SELECT MAX(PROJNAME)
      INTO  :LAST_PROJ
      FROM   PROJECT

   Results in LAST_PROJ being set to WELD LINE PLANNING when using the sample table.


3. Similar to the previous example, set the host variable LAST_PROJ (char(40)) to the project name that comes last in the collating sequence when a project name is concatenated with the host variable PROJSUPP. PROJSUPP is _Support; it has a char(8) data type.

      SELECT MAX(PROJNAME CONCAT PROJSUPP)
      INTO  :LAST_PROJ
      FROM   PROJECT

   Results in LAST_PROJ being set to WELD LINE PLANNING_SUPPORT when using the sample table.