Calculates the smallest value for a column.
Syntax:
MIN([ALL] column_reference | DISTINCT column_reference)
As an aggregate function, MIN performs its calculation aggregating values in the same column(s) across all rows in a dataset. The dataset may be the entire table, a filtered dataset, or a logical group produced by a GROUP BY clause. Column values of zero are included in the aggregation. NULL column values are not counted in the calculation. If the number of qualifying rows is zero, MIN returns a NULL value.
Example:
SELECT MIN(itemstotal) FROM orders
ALL returns the smallest value for all rows. When DISTINCT is not specified, ALL is the implied default.
DISTINCT ignores duplicate values when calculating the smallest value in the specified column.
MIN returns the smallest value in a column or a calculation using a column performed for each row (a calculated field).
Example:
SELECT MIN(itemstotal), MIN(itemstotal * 0.0825) AS LowestTax FROM orders
When used with a GROUP BY clause, MIN returns one calculation value for each group. This value is the aggregation of the specified column for all rows in each group. The statement below aggregates the smallest value for the order totals column in the ORDERS table, producing a subtotal for each company in the COMPANY table.
Example:
SELECT C.company, AVG(O.itemstotal) AS Average,
MAX(O.itemstotal) AS Biggest, MIN(O.itemstotal) AS Smallest
FROM customer C, order O
WHERE (C.custno = O.custno)
GROUP BY C.company
ORDER BY C.company
MIN can be used with all non-BLOB columns. When used with numeric columns, the return value is of the same type as the column (such as INTEGER or FLOAT).
The MIN function cannot be used with memo or BLOB columns.