Collections:
MAX() - Maximum Value in Group
How to calculate the maximum value of a field expression in result set groups using the MAX() function?
✍: FYIcenter.com
MAX(expr) is a MySQL built-in aggregate function that calculates the maximum value of a field expression in result set groups. For example:
SELECT help_category_id, MAX(help_topic_id), COUNT(help_topic_id) FROM mysql.help_topic GROUP BY help_category_id; -- +------------------+--------------------+----------------------+ -- | help_category_id | MAX(help_topic_id) | COUNT(help_topic_id) | -- +------------------+--------------------+----------------------+ -- | 1 | 1 | 2 | -- | 2 | 39 | 35 | -- | 3 | 675 | 59 | -- | 4 | 5 | 2 | -- | 5 | 44 | 3 | -- ... -- +------------------+--------------------+----------------------+ SELECT help_category_id, help_topic_id FROM mysql.help_topic WHERE help_category_id = 5; -- +------------------+---------------+ -- | help_category_id | help_topic_id | -- +------------------+---------------+ -- | 5 | 40 | -- | 5 | 43 | -- | 5 | 44 | -- +------------------+---------------+
MAX() is also a window function, you can call it with the OVER clause to calculate the maximum value of the given expression in the current window. For example:
SELECT help_topic_id, help_category_id, MAX(help_topic_id) OVER w FROM mysql.help_topic WINDOW w AS (PARTITION BY help_category_id); -- +---------------+------------------+---------------------------+ -- | help_topic_id | help_category_id | MAX(help_topic_id) OVER w | -- +---------------+------------------+---------------------------+ -- | 0 | 1 | 1 | -- | 1 | 1 | 1 | -- | 2 | 2 | 39 | -- | 6 | 2 | 39 | -- | 7 | 2 | 39 | -- | 8 | 2 | 39 | -- | 9 | 2 | 39 | -- ... -- +---------------+------------------+---------------------------+
Reference information of the MAX() function:
MAX(expr): max Returns the maximum value of expr. Arguments, return value and availability: expr: Required. The field expression in result set groups. max: Return value. The maximum value of the input expression. Available since MySQL 4.
Related MySQL functions:
⇒ MIN() - Minimum Value in Group
⇐ JSON_OBJECTAGG() - Building JSON Object in Group
2023-11-18, 263🔥, 0💬
Popular Posts:
How To Start MySQL Server in MySQL? If you want to start the MySQL server, you can run the "mysqld" ...
How To Round a Numeric Value To a Specific Precision in SQL Server Transact-SQL? Sometimes you need ...
How to set the current database in SQL Server? Once you are connected to the SQL Server, you should ...
What Happens to Your Transactions When ERROR 1213 Occurred in MySQL? If your transaction receives th...
What Are the Differences between DATE and TIMESTAMP in Oracle? The main differences between DATE and...