MIN()
This page is part of MariaDB's Documentation.
The parent of this page is: Functions for MariaDB Xpand
Topics on this page:
Overview
Returns the minimum value from the aggregated values.
USAGE
MIN([ALL | DISTINCT] value)
Argument Name | Description |
|---|---|
| Optional. The parameters can start with the |
| A value to compare across the aggregated values |
DETAILS
MIN() is an aggregate function that returns the minimum value from a set of values.
The use of the ALL or DISTINCT clause has no effect on what minimum value is computed.
The data type of the argument may be numeric, string, or date/time (DATE, DATETIME, TIMESTAMP).
Any NULL values are ignored when computing the minimum value.
A NULL is returned if no non-NULL values were aggregated.
EXAMPLES
Example Schema and Data
All of the examples are based on the inventory table:
CREATE TABLE inventory (
item VARCHAR(20),
category VARCHAR(20),
count INT,
date DATETIME
);
INSERT INTO inventory VALUES
('Foo', 'in stock', 42, '2020-01-10 10:10:10'),
('Foo', 'back order', 50, '2020-01-10 10:10:10'),
('Bar', 'in stock', 10, '2020-01-10 10:10:30'),
('Bar', 'back order', 60, '2020-01-10 10:10:30'),
('Baz', 'in stock', 99, '2020-01-10 10:10:20'),
('Baz', 'back order', 15, '2020-01-10 10:10:20'),
('FoB', 'in stock', 10, '2020-01-10 10:10:20'),
('FoB', 'back order', NULL, NULL),
('BaB', 'in stock', NULL, NULL),
('BaB', 'back order', NULL, NULL);
The values to be processed include some NULL values that are ignored when computing the minimum value for that field:
SELECT item, category, count, date
FROM inventory;
+------+------------+-------+---------------------+
| item | category | count | date |
+------+------------+-------+---------------------+
| Foo | in stock | 42 | 2020-01-10 10:10:10 |
| Foo | back order | 50 | 2020-01-10 10:10:10 |
| Bar | in stock | 10 | 2020-01-10 10:10:30 |
| Bar | back order | 60 | 2020-01-10 10:10:30 |
| Baz | in stock | 99 | 2020-01-10 10:10:20 |
| Baz | back order | 15 | 2020-01-10 10:10:20 |
| FoB | in stock | 10 | 2020-01-10 10:10:20 |
| FoB | back order | NULL | NULL |
| BaB | in stock | NULL | NULL |
| BaB | back order | NULL | NULL |
+------+------------+-------+---------------------+
The result of processing these values can be requested with or without the "ALL" clause:
SELECT MIN(item) AS item,
MIN(category) AS category,
MIN(count) AS count,
MIN(date) AS date
FROM inventory;
+------+------------+-------+---------------------+
| item | category | count | date |
+------+------------+-------+---------------------+
| BaB | back order | 10 | 2020-01-10 10:10:10 |
+------+------------+-------+---------------------+
With GROUP BY
Using MIN() with GROUP BY creates a separate minimum for each group:
SELECT category, MIN(count)
FROM inventory
GROUP BY category
ORDER BY category;
+------------+------------+
| category | MIN(count) |
+------------+------------+
| back order | 15 |
| in stock | 10 |
+------------+------------+
With Only NULL Values
When the result is either an empty set of rows or only NULL values, the minimum value is NULL:
SELECT MIN(count)
FROM inventory
WHERE category = 'Does Not Exist';
+------------+
| MIN(count) |
+------------+
| NULL |
+------------+
SELECT MIN(count)
FROM inventory
WHERE count IS NULL;
+------------+
| MIN(count) |
+------------+
| NULL |
+------------+