Mysql Max

Syntax of MySQL MAX() Function

The syntax for the MySQL max function is really simple and looks something like this

This function also supports one more optional syntax which looks something like this.

Now let us understand this syntax and parameters in detail in the next section

Parameters of MySQL MAX() Function

Expression: The MySQL max() function takes mostly this one parameter which signifies the expression or column or field that you want to find the maximum of. It can be anything from a column name, a mathematical expression, or a date/time data type supported by MySQL, such as INT, DECIMAL, FLOAT, DATE, TIME, DATETIME, TIMESTAMP, etc.

DISTINCT: This optional keyword is primarily used to remove duplicates from your data set so that you won't have to compare the same values from multiple rows in a column.

Over_clause: The over clause is an optional parameter. The over clause is only used when you haven't explicitly mentioned the DISTICT keyword in your MySQL max() function.

Return Value of MySQL MAX() Function

The MySQL MAX() function returns a single value, which is the maximum value found in the specified column or expression. The data type of the returned value depends on the data type of the input column or expression. For example, if the input column is of type INT, the returned value will be an INT, if the input column is of type DECIMAL, the returned value will be a DECIMAL, and so on.

Exceptions of MySQL MAX() Function

• It is important to note that if the input column or expression contains NULL values, the MAX() function will return NULL as the result. Therefore, it is necessary to handle NULL values appropriately in your queries to avoid unexpected results.
• Another thing to note here is that it operates on a single column or expression at a time and returns the maximum value found in that column or expression.
• The MAX() function ignores any NULL values in the input column or expression when determining the maximum value.

How does the MySQL MAX() Function Work?

The MySQL max() function is part of the aggregate function family in MySQL which is a collection of functions that work on an expression or a group of values from a column, apply logical calculations to them, and return the final answer. MySQL max() function is used to find the maximum value from an expression or a column in the same way. It takes in one parameter and that is the column that we are trying to find the maximum of. Then it goes through each row of our table and checks the corresponding value for the specified column. In the end, it returns the maximum value in that column.

For example, if you have a table that stores the salaries of every employee in your company, you can apply the max() function on the Salary column and it will return the employee with the highest salary.

We will look at some examples in the next section to fully grasp the concept.

Examples

So, we have seen the syntax for the MySQL max() function and we have also seen how the MySQL max() function works. Now it's time to understand the concept in detail by looking at some actual examples. In this section, you will see different cases where you can use the max() function and how to apply it. Let us get started

Example 1:
Using MySQL max() function

Let us look at a simple example first. let us consider the customer's table which looks something like this.

Customers

Now let us say that we want to find the oldest customer in our table. We can do that with the help of MySQL max() like this.

Output

As you can see, our query returns the highest value of age in our entire table.

Example 2:
Using MySQL max() function along with the GROUP BY clause

In the last example, we saw how we can use the max() function to find the oldest customer in our Customers table. But let us say that we are conducting a survey we want to find the oldest person from each country? Let us look at an example that does exactly that.

So our Customers table looks something like this.

Customers:

Let's find the oldest person from each country

Output

So as you can see, we can get the maximum age grouped by each country with the help of the MySQL max() function.

Example 3:
Using MySQL max() function along with the WHERE clause

Now Let us assume we want to calculate the Highest age of the person in the country USA. In that case, we can use the MySQL max() function along with the Where clause.

Customers:

Output

As you can see, we get the highest age in the USA.

Example 4:
Using MySQL max() function along with LENGTH() function

Let us now look at a slightly interesting example. Lets us assume that you work at an online store and you have a table that stores the information regarding all your products. The product table looks something like this.

Products

Now you want to find out the product with the longest name for some reason. You can do that by using the MySQL max() function in combination with another MySQL function called LENGTH(). The LENGTH() function returns the length of the expression provided to it.

Let us see what the code for the same looks like.

Output

As you can see, the MySQL max() function calculates the length of each product name and returns us the maximum length which is 31 for Uncle Bob's Organic Dried Pears.

Example 5:
Using MySQL max() function along with the HAVING clause

Now let us look at an example where we can use the Having clause along with the MySQL max() function. If you remember, having a clause is always used along with the group by clause.

Let us suppose that we have a suppliers table that looks something like this.

Suppliers

now, we are trying to find the maximum length of a supplier name in every country living in a city that contains an 'o' in its name. This sounds a little complex but we can get the desired result with the help of these simple MySQL queries.

Output:

We can notice that we get the name and length count of the name of each supplier which lives in a city containing the letter 'o' in its name in each country.

Example 6:
Using MySQL max() function along with nested subqueries

Now let us see how we can use the MySQL max() function along with the nested subqueries. Let us assume that we have an orders table that looks something like this.

OrderDetails

Output

In this example, we are using a nested query to get all the orderIDs in a particular range, and then out of those ids, we are getting the maximum productID by using the MySQL max() function.

See also

With this, we have come to the end of this article about the MySQL max() function. But learning never stops and neither should you. So, if you want to sharpen your skills, check out these similar articles on scaler topics.

• How to Find Length in SQL?
• Types of Subqueries in SQL
• Group By clause in sql
• Having clause in sql
• Clauses in SQL