MAX aggregate function

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

The schema is SYSIBM.

The arguments must be compatible. For more information on compatibility, refer to the compatibility matrix in Table 1. All arguments except the first argument can be parameter markers.

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.

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.

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.

Examples

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

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

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

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