SQL Reference

MAX

             .-ALL------.
>>-MAX--(----+----------+--expression--)-----------------------><
             '-DISTINCT-'
 

The schema is SYSIBM.

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

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

If DISTINCT is used, the resulting data type of expression must not have a length greater than 255 for a character column or 127 for a graphic column. The resulting data type of expression cannot be a LONG VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, DBCLOB, DATALINK, distinct type on any of these types, or structured type (SQLSTATE 42907).

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.

Examples:

• 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.

• 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.

• 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.