MAX

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


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

The schema is SYSIBM.

Start of change The arguments must be compatible. For more information on compatibility, refer to the compatibility matrices in Assignment and comparison. All arguments except the first argument can be parameter markers. End of change

This function cannot be used as a source function when creating a user-defined function.

expression: An expression that returns the value of a built-in data type. Each expression must return a value that is not a CLOB, DBCLOB, BLOB, ROWID, or XML. Character string arguments and binary string arguments cannot have a length attribute greater than 32704, and graphic string arguments cannot have a length attribute greater than 16352.
If there are any mixed character string or graphic string and numeric arguments, the string value is implicitly cast to a DECFLOAT(34) value.

Start of change The result of the function is the largest argument value. The data type of the result and its other attributes (for example, the length and CCSID of a string or a datetime value) are the same as the data type and attributes of the argument values. The result can be null. End of change

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 the null value. Otherwise, the result is the maximum value in the set.

The specification of DISTINCT has no effect on the result and is not advised.

Example 1: Set the DECIMAL(8,2) variable MAX_SALARY to the maximum monthly salary of the employees represented in the sample table DSN8A10.EMP.

   EXEC SQL SELECT MAX(SALARY) / 12
     INTO :MAX_SALARY
     FROM DSN8A10.EMP;

Example 2: Find the surname that comes last in the collating sequence for the employees represented in the sample table DSN8A10.EMP. Set the VARCHAR(15) variable LAST_NAME to that surname.

   EXEC SQL SELECT MAX(LASTNAME)
     INTO :LAST_NAME
     FROM DSN8A10.EMP;