Thanks for your feedback!
Need to tell us more? Click here or use the Feedback button.

Is this page helpful?

Contents

Synopsis
Description
Arguments
Changes Made During the Current Transaction
Examples
See Also

MIN (SQL)

An aggregate function that returns the minimum data value in a specified column.

Synopsis

MIN([ ALL | DISTINCT [BY(col-list)] ]
  expression 
  [ %FOREACH(col-list) ] [ %AFTERHAVING ])

Description

The MIN aggregate function returns the smallest (minimum) of the values of expression. Commonly, expression is the name of a field, (or an expression containing one or more field names) in the multiple rows returned by a query.

MIN can be used in a SELECT query or subquery that references either a table or a view. MIN can appear in a SELECT list or HAVING clause alongside ordinary field values.

MIN cannot be used in a WHERE clause. MIN cannot be used in the ON clause of a JOIN, unless the SELECT is a subquery.

Like most other aggregate functions, MIN cannot be applied to a stream field. Attempting to do so generates an SQLCODE -37 error.

Unlike most other aggregate functions, the ALL and DISTINCT keywords, including MIN(DISTINCT BY(col2) col1), perform no operation in MIN. They are provided for SQL–92 compatibility.

Data Values

The specified field used by MIN can be numeric or nonnumeric. For a numeric data type field, minimum is defined as lowest in numeric value; thus -7 is lower than -3. For a non-numeric data type field, minimum is defined as lowest in string collation sequence; thus '-3' is lower than '-7'.

An empty string ('') value is treated as CHAR(0).

A predicate uses the collation type defined for the field. By default, string data type fields are defined with SQLUPPER collation, which is not case-sensitive. You can define the string collation default for the current namespace and specify a non-default field collation type when defining a field/property.

When the field’s defined collation type is SQLUPPER, MIN returns strings in all uppercase letters. Thus SELECT MIN(Name) returns 'AARON', regardless of the original lettercase of the data. But because comparisons are performed using uppercase collation, the clause HAVING Name=MIN(Name) selects rows with the Name value 'Aaron', 'AARON', and 'aaron'.

For numeric values, the scale returned is the same as the expression scale.

NULL values in data fields are ignored when deriving a MIN aggregate function value. If no rows are returned by the query, or the data field value for all rows returned is NULL, MIN returns NULL.

Arguments

ALL

An optional argument that applies the aggregate function to all values. ALL has no effect on the value returned by MIN. It is provided for SQL-92 compatibility.

DISTINCT

An optional argument specifying that each unique value is considered. DISTINCT has no effect on the value returned by MIN. It is provided for SQL-92 compatibility.

expression

Any valid expression. Usually the name of a column that contains the values from which the minimum value is to be returned.

%FOREACH(col-list)

An optional column name or a comma-separated list of column names. See SELECT for further information on %FOREACH.

%AFTERHAVING

An optional argument that applies the condition found in the HAVING clause.

MIN returns the same data type as expression.

Note:

MIN can be specified as an aggregate function or as a window function. This reference page describes the use of MIN as an aggregate function. MIN as a window function is described in Overview of Window Functions.

Changes Made During the Current Transaction

Like all aggregate functions, MIN always returns the current state of the data, including uncommitted changes, regardless of the current transaction’s isolation level. For further details, refer to SET TRANSACTION and START TRANSACTION.

Examples

In the following examples a dollar sign ($) is concatenated to Salary amounts.

The following query returns the lowest (minimum) salary in the Sample.Employee database:

SELECT '$' || MIN(Salary) AS LowSalary
     FROM Sample.Employee

The following query returns one row for each state that contains at least one employee with a salary larger than $75,000. Using the %AFTERHAVING keyword, each row returns the minimum employee salary larger than $75,000. Each row also returns the minimum salary and the maximum salary for all employees in that state:

SELECT Home_State,
       '$' || MIN(Salary %AFTERHAVING) AS MinSalaryAbove75K,
       '$' || MIN(Salary) AS MinSalary,
       '$' || MAX(Salary) AS MaxSalary
          FROM Sample.Employee
     GROUP BY Home_State
     HAVING Salary > 75000
     ORDER BY MinSalaryAbove75K

The following query returns the lowest (minimum) and highest (maximum) name in collation sequence found in the Sample.Employee database:

SELECT Name,MIN(Name),MAX(Name)
     FROM Sample.Employee

Note that MIN and MAX convert Name values to uppercase before comparison.

The following query returns the lowest (minimum) salary for an employee whose Home_State is 'VT' in the Sample.Employee database:

SELECT MIN(Salary)
     FROM Sample.Employee
     WHERE Home_State = 'VT'

The following query returns the number of employees and the lowest (minimum) employee salary for each Home_State in the Sample.Employee database:

SELECT Home_State, 
     COUNT(Home_State) As NumEmployees, 
     MIN(Salary) As LowSalary
     FROM Sample.Employee
     GROUP BY Home_State
     ORDER BY LowSalary