How the MAX() function works in Mariadb?

The MAX() function is a useful tool for finding the maximum value in a set of values.

Posted on

The MAX() function is a useful tool for finding the maximum value in a set of values. It can be used to find the maximum value in a column, a row, a group, or an expression. It can also be used with other aggregate functions, such as SUM(), COUNT(), or AVG(), to perform more complex calculations.

Syntax

The syntax of the MAX() function is as follows:

MAX([DISTINCT] expression)

The function takes one argument:

  • expression: A numeric or string expression that can be a column name, a constant, a function, or a combination of them. If the expression is a string, the function will compare the values based on the collation of the column or the connection.
  • DISTINCT: An optional keyword that specifies that only distinct values are considered. If omitted, all values are considered, including duplicates and NULLs.

The function returns the maximum value of the expression, or NULL if the expression is empty or contains only NULLs.

Examples

In this section, we will show some examples of how to use the MAX() function in different scenarios.

Example 1: Finding the maximum value in a column

Suppose you have a table called products that stores the information of various products, such as their name, price, and category. You want to find the most expensive product in the table. You can use the MAX() function to find the maximum value in the price column. For example, you can execute the following statement:

SELECT MAX(price) AS max_price FROM products;

This will return the maximum price of all the products in the table, or NULL if the table is empty or the price column contains only NULLs. For example, the result might look like this:

+-----------+
| max_price |
+-----------+
|    99.99  |
+-----------+

Example 2: Finding the maximum value in a row

Suppose you have a table called scores that stores the scores of three students in four subjects: math, physics, chemistry, and biology. You want to find the highest score of each student in any subject. You can use the MAX() function to find the maximum value in a row. For example, you can execute the following statement:

SELECT name, MAX(math, physics, chemistry, biology) AS max_score FROM scores;

This will return the name and the highest score of each student in any subject, or NULL if the row contains only NULLs. For example, the result might look like this:

+------+-----------+
| name | max_score |
+------+-----------+
| Alice|        95 |
| Bob  |        90 |
| Carol|        92 |
+------+-----------+

Example 3: Finding the maximum value in a group

Suppose you have a table called orders that stores the information of various orders, such as their date, customer, and amount. You want to find the largest order amount for each customer. You can use the MAX() function with the GROUP BY clause to find the maximum value in a group. For example, you can execute the following statement:

SELECT customer, MAX(amount) AS max_amount FROM orders GROUP BY customer;

This will return the customer and the largest order amount for each customer, or NULL if the group is empty or the amount column contains only NULLs. For example, the result might look like this:

+----------+------------+
| customer | max_amount |
+----------+------------+
| John     |      50.00 |
| Mary     |      75.00 |
| Peter    |      60.00 |
+----------+------------+

Example 4: Finding the maximum value in an expression

Suppose you have a table called employees that stores the information of various employees, such as their name, salary, and bonus. You want to find the highest total income of any employee, where the total income is the sum of the salary and the bonus. You can use the MAX() function with an expression to find the maximum value in an expression. For example, you can execute the following statement:

SELECT MAX(salary + bonus) AS max_income FROM employees;

This will return the highest total income of any employee, or NULL if the table is empty or the expression contains only NULLs. For example, the result might look like this:

+------------+
| max_income |
+------------+
|    1200.00 |
+------------+

There are some other functions that are related to the MAX() function and can be used to find other statistics or perform other calculations. Here are some of them:

  • MIN(): This function returns the minimum value in a set of values. It has the same syntax and usage as the MAX() function, except that it returns the smallest value instead of the largest value.
  • AVG(): This function returns the average value in a set of values. It has the same syntax and usage as the MAX() function, except that it returns the sum of the values divided by the number of values instead of the largest value.
  • SUM(): This function returns the sum of the values in a set of values. It has the same syntax and usage as the MAX() function, except that it returns the sum of the values instead of the largest value.
  • COUNT(): This function returns the number of values in a set of values. It has the same syntax and usage as the MAX() function, except that it returns the number of values instead of the largest value.

Conclusion

The MAX() function is a powerful and flexible function that can help you find the maximum value in a set of values. You can use it to find the maximum value in a column, a row, a group, or an expression. You can also use it with other aggregate functions, such as SUM(), COUNT(), or AVG(), to perform more complex calculations. By using this function, you can achieve a better analysis and understanding of your data.