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, 261🔥, 0💬
Popular Posts:
What Is an Oracle Tablespace in Oracle? An Oracle tablespace is a big unit of logical storage in an ...
How To Format DATETIME Values to Strings with the CONVERT() Function in SQL Server Transact-SQL? SQL...
How to continue to the next iteration of a WHILE loop in SQL Server Transact-SQL? How to use CONTINU...
How To List All User Names in a Database in SQL Server? If you want to see a list of all user names ...
Is SQL Server Transact-SQL case sensitive? No. Transact-SQL is not case sensitive. Like the standard...