DB2 Version 10.1 for Linux, UNIX, and Windows

MAX aggregate function

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


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

The schema is SYSIBM.

expression: An expression that returns a value of any built-in data type other than a BLOB, CLOB, DBCLOB, or XML.

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.

Notes

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

Examples

Example 1: 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.
Example 2: 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.
Example 3: 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.