Updated May 10, 2023
Introduction to MySQL MAX() Function
MySQL MAX() function is an SQL query that returns the maximum value in a set of values from the expression in a database table. Normally, MAX() is an aggregate function that we can use to calculate over a range of values the SELECT statement returns to find the maximum one from those separated rows in the table. The function returns a NULL value if no condition matches. Suppose we can use this function to find out the largest population of a city from a list of cities. Also, the MAX() function helps in many cases, such as getting the highest number, the most costly item, and the most significant payment from consumers.
Syntax of MAX() with Explanation
The basic syntax of the MAX() function in MySQL goes like this:
MAX( [DISTINCT] Expression)The term ‘Expression’ denotes a numeric value that can either be a field in a table or a formula, which is required. This keyword allows for removing the duplicate values from the list of records in the database table. But almost it will be the same result without the DISTINCT keyword too.
Also,
MAX([DISTINCT] expression) [over_clause]Here, the over_clause is a non-compulsory part associated with window tasks. That is why over_clause is used only if you don’t add the DISTINCT keyword in the MAX() function.
How does MAX() Function Work in MySQL?
As we have learned that MAX() function finds out the maximum value from total rows selected by the SELECT statement, we can now easily get the benefits in our business or product management. For example, it will allow getting the high-selling product of your company or the maximum quantity of the product in your store consumed by the customers.
We can apply this query in our Databases which returns the result. MySQL MAX() SQL statement is shown below:
SELECT MAX ([Column_Name]) FROM [Source]Therefore, using MAX() function, the maximum value present in the specified column can be returned from a database table as a source.
Examples to Implement MySQL MAX() Function
Let us see the MAX() function by the following examples and types:
1. To Find the Maximum Value in Column
Let us take the below-shown records from a table of Products.
After checking all product prices, we have used the MAX() to get the product whose price is higher than others.
Query:
SELECT MAX(Price) AS LargestPrice FROM Products;Output:
2. With WHERE Clause
Here we use MAX() to return the largest value from a particular condition using the WHERE keyword with SELECT.
Query:
SELECT MAX(Price) AS LargestPrice FROM Products WHERE CategoryID=3;Output:
We have got the largest value for only the price whose CategoryID is three from the Products table. We can also use any multi-word alias (like LargestPrice) to define the highest value, as in the above query.
3. With Sub-Query Statement
To provide not only the maximum price from the products table but also to show the row information, including other fields, we have used MAX() in a subquery form as follows:
Query:
SELECT * From Products WHERE Price = (SELECT MAX(Price) AS LargestPrice FROM Products);Output:
The inner query returns the largest price, and the outer query provides the row information of the largest price field in the products table.
4. With GROUP BY Clause
This function with the GROUP BY clause helps to find out the maximum value for every group based on the criteria.
Query:
SELECT SupplierID , MAX(Price) FROM Products GROUP BY SupplierID;Output:
SupplierID groups the product fields from the Products table, but the maximum prices are arranged randomly, not ascending or descending.
5. With ORDER BY Clause
This function with the ORDER BY clause helps specify the column we can order by the maximum value for every group based on the criteria.
Query:
SELECT SupplierID , MAX(Price) FROM Products
GROUP BY SupplierID
ORDER BY MAX(price);Output:
Now, the fields are grouped by SupplierID, and the result set is ordered based on MAX(Price) in ascending order, where the minimum value is set first and the maximum last.
6. With HAVING Clause
In the previous example, we used the GROUP BY clause with MAX() function, where we got the maximum price for every group, but now we can use the HAVING clause with MAX() function to filter these groups based on a specific condition.
Query:
SELECT SupplierID , MAX(Price) FROM Products
GROUP BY SupplierID
HAVING MAX(Price) >90
ORDER BY MAX(price);Output:
In the above query, firstly, we have used MAX() to get the highest price from a group of Products with GROUP BY clause association, and again based on the HAVING clause, we have applied the MAX function to get the highest prices which are greater than 50 in the table. Next, suppose we want names of suppliers instead of supplier ids; then, we can even use the JOIN clause with the above query. We need to apply INNER JOIN for both the tables, Products, and Suppliers.
Here is the Suppliers table data for supplier ID 1,2,3 used in the example:
Query:
SELECT SupplierName, MAX(Price) FROM Products
INNER JOIN Suppliers
USING (SupplierID)
GROUP BY SupplierName
HAVING MAX(Price) >50
ORDER BY MAX(price);Output:
7. To Find the Maximum Character Length
With MAX(), we can find out the maximum value from numeric data in the column field and implement it in other areas. We can combine MySQL MAX() function with other functions like CHAR_LENGTH. For example, taking the same Products table, we can write the following query and find the maximum number of characters in the ProductName column:
Query:
SELECT MAX(CHAR_LENGTH(ProductName)) AS 'Max Char Length'
FROM Products;Output:
Conclusion
Thus, in this article hope you all might have explored some important usages of the MAX() function in MySQL with syntax and examples that will help you to deal with the functional queries related to databases and tables in the areas of business or any industry with huge data stored. You can practice them for good command over them.
Recommended Articles
We hope that this EDUCBA information on “MySQL MAX() Function” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
