MySQL MAX 函数

Summary: in this tutorial, you will learn how to use the MySQL MAX function to get the maximum value in a set of values.

Introduction to MySQL MAX function

The MySQL MAX function returns the maximum value in a set of values. The MAX function is handy in many queries such as finding the greatest number, the most expensive product, and the largest payment from customers.

The syntax of the MAX function is as follows:

MAX(DISTINCT expression)

If you add the DISTINCT operator, the MAX function returns the maximum value of distinct values, which is the same as the maximum value of all values. It means the DISTINCT operator does not take any effect in the MAX function.

Notice that DISTINCT operator takes effect in other aggregate functions such as COUNT, SUM, and AVG.

MySQL MAX function examples

Let’s take a look at the payments table in the sample database.

To get the largest payment in the payments table, you use the following query:

SELECT MAX(amount) FROM payments;

MySQL MAX function in subquery

To get not only the largest payment’s amount but also other payment’s information such as customer number, check number and payment date, you use the MAX function in a subquery as follows:

SELECT * FROM payments WHERE amount = (       SELECT MAX(amount)       FROM payments );

How it works.

• The subquery returns the largest amount of all payments.

• The outer query gets the payment whose  amount is equal to the largest amount returned from the subquery and also other related payment’s information.

Another way to do this without using the MAX function is to sort the result set in descending order by using the ORDER BY clause and get the first row by using the LIMIT clause as the following query:

SELECT * FROM payments ORDER BY amount DESC  LIMIT 1;

If you don’t index the amount column, the second query executes faster because it examines all rows in the payments table, while the first query examines all the rows in the payments table twice, one in the subquery and one in the outer query. However, if the amount column is indexed, the first query will perform faster.

MySQL MAX with GROUP BY clause

To find the maximum value for every group, you use the MAX function with the GROUP BY clause in a SELECT statement.

For each customer, get the largest payment that the customer has been paid, you use the following query:

SELECT customerNumber, MAX(amount) FROM payments GROUP BY customerNumber ORDER BY MAX(amount);

MySQL MAX with HAVING clause

You use the MAX function in a HAVING clause with the GROUP BY clause to add a filter for groups based on a specified condition.

For example, the following query finds the largest payment of each customer; and based on the returned payments, get only payments whose amounts are greater than 80000.

SELECT customerNumber, MAX(amount) FROM payments GROUP BY customerNumber HAVING MAX(amount) > 80000;

In this tutorial, we have shown you how to use the MySQL MAX function to find the maximum value in a set of values.

原文链接：http://outofmemory.cn/mysql/function/mysql-max-function