MAX()
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 maximum value from the aggregated values.
USAGE
MAX([ALL | DISTINCT] value)
Argument Name | Description |
|---|---|
| Optional. The parameters can start with the |
| A value to compare across the aggregated values |
DETAILS
MAX() is an aggregate function that returns the maximum value for a set of values.
The use of the ALL or DISTINCT clause has no effect on what maximum value is computed.
The data type of the argument may numeric, string, or date/time (DATE, DATETIME, TIMESTAMP).
Any NULL values are ignored when computing the maximum value.
A NULL is returned if no non-NULL values were aggregated.
EXAMPLES
Example Schema and Data
Some 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 maximum 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 |
+------+------------+-------+---------------------+
SELECT MAX(item) AS item,
MAX(category) AS category,
MAX(count) AS count,
MAX(date) AS date
FROM inventory;
+------+----------+-------+---------------------+
| item | category | count | date |
+------+----------+-------+---------------------+
| Foo | in stock | 99 | 2020-01-10 10:10:30 |
+------+----------+-------+---------------------+
With GROUP BY
Using MAX() with GROUP BY creates a separate maximum for each group:
SELECT category, MAX(count)
FROM inventory
GROUP BY category
ORDER BY category;
+------------+------------+
| category | MAX(count) |
+------------+------------+
| back order | 60 |
| in stock | 99 |
+------------+------------+
With Only NULL Values
When the result is either an empty set of rows or only NULL values, the maximum value is NULL:
SELECT MAX(count)
FROM inventory
WHERE category = 'Does Not Exist';
+------------+
| MAX(count) |
+------------+
| NULL |
+------------+
SELECT MAX(count)
FROM inventory
WHERE count IS NULL;
+------------+
| MAX(count) |
+------------+
| NULL |
+------------+