SQL Reference

MIN

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

The schema is SYSIBM.

The MIN function returns the minimum 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 data type of expression must not have a length greater than 255 for a character column or 127 for a graphic column. The resulting data type of expression cannot be a LONG VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, DBCLOB, DATALINK, distinct type on any of these types, or structured type (SQLSTATE 42907).

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.

Examples:

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

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