DB2 Version 10.1 for Linux, UNIX, and Windows

MIN aggregate function

The MIN function returns the minimum value in a set of values.


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

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 this function is applied to an empty set, the result of the function is a null value. Otherwise, the result is the minimum 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 minimum 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 COMM_SPREAD (decimal(7,2)) to the difference between the maximum and minimum commission (COMM) for the members of department (WORKDEPT) 'D11'.
```
   SELECT MAX(COMM) - MIN(COMM)
     INTO :COMM_SPREAD
     FROM EMPLOYEE
     WHERE WORKDEPT = 'D11'
```
Results in COMM_SPREAD being set to 1118 (that is, 2580 - 1462) when using the sample table.
Example 2: Using the PROJECT table, set the host variable (FIRST_FINISHED (char(10)) to the estimated ending date (PRENDATE) of the first project scheduled to be completed.
```
   SELECT MIN(PRENDATE)
     INTO :FIRST_FINISHED
     FROM PROJECT
```
Results in FIRST_FINISHED being set to '1982-09-15' when using the sample table.