EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL MAX() Function

MySQL MAX() Function

Aanchal Sharma
Article byAanchal Sharma
Priya Pedamkar
Reviewed byPriya Pedamkar

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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
ANSIBLE Course Bundle - 4 Courses in 1
11+ Hour of HD Videos
4 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
LIGHTWORKS Course Bundle - 2 Courses in 1
18+ Hours of HD Videos
2 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
AUDACITY Course Bundle - 1 Course | 1 Mock Test
8+ Hours of HD Videos
1 Courses
1 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
UNITY Course Bundle - 26 Courses in 1 | 5 Mock Tests
109+ Hours of HD Videos
26 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
3DS MAX Architecture Course Bundle - 4 Courses in 1 | 3 Mock Tests
 11+ Hours of HD Videos
4 Courses
3 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

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

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more