EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL MAX() Function
 

MySQL MAX() Function

Aanchal Sharma
Article byAanchal Sharma
EDUCBA
Reviewed byRavi Rathore

Updated May 10, 2023

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 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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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.

MySQL max() Function output 1

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:

MySQL max() Function output 1.2

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:

MySQL max() Function output 2

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:

MySQL max() Function output 3

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:

MySQL max() Function output 4

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:

output 5

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.

Note: We can also use ASC or DESC to set an order BY clause like “ORDER BY MAX(Price) ASC”.

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:

output 6

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:

output 6.2

Query:

SELECT SupplierName, MAX(Price) FROM Products
INNER JOIN Suppliers
USING (SupplierID)
GROUP BY SupplierName
HAVING MAX(Price) >50
ORDER BY MAX(price);

Output:

output 6.3

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:

output 7

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.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW