MySQL MAX() Function

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 returned by the SELECT statement to find out the maximum one from those separated rows in the table. The function returns a NULL value if there is no condition that matches. Suppose we can use this function for finding out the largest population of a city from a list of cities. Also, MAX() function helps in dealing with many cases such as to get the highest number, the most costly item and the biggest payment from consumers.

Syntax of MAX() with Explanation

The basic syntax of MAX() function in MySQL goes like this:

MAX( [DISTINCT] Expression)

Where 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 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 which is 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, then we can now easily get the benefits in our business or in 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.

Simply 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 form 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 Products.

We have used the MAX() to get the product whose price is higher than others after checking all products prices.

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 WHERE keyword with SELECT.

Query:

SELECT MAX(Price) AS LargestPrice FROM Products WHERE CategoryID=3;

Output:

We have got the largest value for only price whose CategoryID is 3 form Products table. We can use any multi-word alias (like LargestPrice) too to define the highest value as in the above query.

3. With Sub-Query Statement

To provide not only the maximum price from 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:

The product fields are grouped by SupplierID from Products table but the maximum prices are arranged randomly not either ascending or descending.

5. With ORDER BY Clause

This function with the ORDER BY clause helps to specify the column with which 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 on the basis of MAX(Price) in ascending order where the minimum value is set first and maximum at last.

6. With HAVING Clause

In the previous example, we have 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 certain 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 if 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 ids 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 cannot just find out the maximum value from numeric data in the column field but also can implement it to other areas. We can combine MySQL MAX() function with some 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 MAX() function in MySQL with syntax and examples that will help you to deal with the functional queries related to database and tables with the areas of business or any industry with huge data stored. You can practice them for good command over it.

Recommended Articles

This is a guide to the MySQL MAX() Function. Here we discuss how does MySQL MAX() Function works along with example using different conditions. You may also look at the following articles to learn more-

1. How does the NOT Condition Works in MySQL?
2. Condition in MySQL
3. How to Create Views in MySQL?
4. Top 11 Aggregate Functions