The MAX aggregate function returns the maximum value in a set of values in a group.
The argument values can be any built-in data type other
than a DataLink or XML.
The data type and length attribute of the result are the same as the data type and length attribute of the argument values. When the argument is a string, the result has the same CCSID as the argument.
If a collating sequence other than *HEX is in effect when the statement that contains the MAX function is executed and the arguments are SBCS data, mixed data, or Unicode data, then the result is obtained by comparing weighted values for each value in the set. The weighted values are based on the collating sequence.
The function is applied to the set of values derived from the argument values by the elimination of null values.
The result can be null. If the function is applied to the 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 is not advised.
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. See Numeric comparisons. 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.
SELECT MAX(SALARY) /12 INTO :MAX_SALARY FROM EMPLOYEEResults in MAX_SALARY being set to 4395.83.
SELECT MAX(PROJNAME) INTO :LAST_PROJ FROM PROJECTResults in LAST_PROJ being set to 'WELD LINE PLANNING '.