MAX (aggregate)

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

Read syntax diagramSkip visual syntax diagramMAX(ALLDISTINCTexpression)

The schema is SYSIBM.

Start of changeThe 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.End of change

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

Examples

Example 1: Set the DECIMAL(8,2) variable MAX_SALARY to the maximum monthly salary of the employees represented in the sample table DSN8B10.EMP.
   EXEC SQL SELECT MAX(SALARY) / 12
     INTO :MAX_SALARY
     FROM DSN8B10.EMP;
Example 2: Find the surname that comes last in the collating sequence for the employees represented in the sample table DSN8B10.EMP. Set the VARCHAR(15) variable LAST_NAME to that surname.
   EXEC SQL SELECT MAX(LASTNAME)
     INTO :LAST_NAME
     FROM DSN8B10.EMP;