SQL Reference

MAX

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

The schema is SYSIBM.

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

The argument values can be of any built-in type other than a long string or DATALINK.

If DISTINCT is used, the resulting expression must not have a length greater than 254 for a character column or 127 for a graphic column.

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.

Examples:

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

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]