How to query the maximum value in mysql

In mysql, you can use the "SELECT" statement and the MAX() function to query the maximum value. This function can return the maximum value in a set of values. The syntax "SELECT MAX(DISTINCT expression) FROM data table name ;".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

In mysql, you can use the "SELECT" statement and the MAX() function to query the maximum value.

MySQL MAX()The function returns the maximum value in a set of values. The MAX() function is very convenient in many queries, such as finding the maximum quantity, the most expensive product, and the maximum payment by a customer.

MAX()The syntax of the function is as follows:

MAX(DISTINCT expression);

If you add the DISTINCT operator, the MAX function returns a different The maximum value of a value, which is the same as the maximum value of all values. This means that the DISTINCT operator will not have any impact on the MAX function (it does not matter whether the DISTINCT operator is used or not).

Please note that the DISTINCT operator takes effect within other aggregation functions such as COUNT, SUM, and AVG.

MySQL MAX function example

Let’s take a look at the payments table in the sample database (yiibaidb). Its table structure is as follows -

mysql> desc payments;
+----------------+---------------+------+-----+---------+-------+
| Field          | Type          | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| customerNumber | int(11)       | NO   | PRI | NULL    |       |
| checkNumber    | varchar(50)   | NO   | PRI | NULL    |       |
| paymentDate    | date          | NO   |     | NULL    |       |
| amount         | decimal(10,2) | NO   |     | NULL    |       |
+----------------+---------------+------+-----+---------+-------+
4 rows in set

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

SELECT 
    MAX(amount)
FROM
    payments;

Execute the above query statement and get the following results-

mysql> SELECT 
    MAX(amount)
FROM
    payments;
+-------------+
| MAX(amount) |
+-------------+
| 120166.58   |
+-------------+
1 row in set

MySQL MAX function in subquery

To get not only the maximum payment amount, but also other payment information such as customer number, check number and payment date, you can do it in the subquery Use the MAX function as follows:

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

Execute the above query statement and get the following results-

+----------------+-------------+-------------+-----------+
| customerNumber | checkNumber | paymentDate | amount    |
+----------------+-------------+-------------+-----------+
|            141 | JE105477    | 2015-03-18  | 120166.58 |
+----------------+-------------+-------------+-----------+
1 row in set

How does the above query statement run?

• Subquery returns all maximum payment amounts.
• The outer query retrieves other related payment information whose amount is equal to the maximum amount returned from the subquery.

An alternative to not using the MAX function is to use the ORDER BY clause to sort the result set in descending order and use LIMIT clause gets the first row, query as follows:

SELECT 
    *
FROM
    payments
ORDER BY amount DESC
LIMIT 1;

Execute the above query statement and get the following results-

+----------------+-------------+-------------+-----------+
| customerNumber | checkNumber | paymentDate | amount    |
+----------------+-------------+-------------+-----------+
|            141 | JE105477    | 2015-03-18  | 120166.58 |
+----------------+-------------+-------------+-----------+
1 row in set

If you have not created on the amount column index, the second query executes faster because it checks all rows in the payments table, whereas the first query checks all rows in the payments table twice , once in the subquery and once in the outer query. However, if the amount column is indexed, the first query will execute faster.

MySQL MAX with GROUP BY clause

To find the maximum value for each group, you can do it in SELECTUse MAX function and GROUP BY clause in the statement.

For each customer, query to obtain the maximum payment the customer has paid, you can use the following query:

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

Execute the above query statement and get the following query results-

+----------------+-------------+
| customerNumber | MAX(amount) |
+----------------+-------------+
|            219 | 4465.85     |
|            198 | 9658.74     |
|            381 | 14379.9     |
|            103 | 14571.44    |
|            473 | 17746.26    |
|            362 | 18473.71    |
******* 此处省略一大波数据 ******
|            148 | 105743      |
|            124 | 111654.4    |
|            141 | 120166.58   |
+----------------+-------------+
98 rows in set

MySQL MAX with HAVING clause

You can use MAX## in the HAVING clause #Function to add filters to groups based on specified conditions. For example, the following query finds the maximum payment for each customer; and based on the returned amount, such as the following query statement, only queries for payments exceeding 80000

-

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

is executed The above query statement yields the following results -

+----------------+-------------+
| customerNumber | MAX(amount) |
+----------------+-------------+
|            114 | 82261.22    |
|            124 | 111654.4    |
|            141 | 120166.58   |
|            148 | 105743      |
|            167 | 85024.46    |
|            239 | 80375.24    |
|            321 | 85559.12    |
+----------------+-------------+
7 rows in set

[Related recommendations:

mysql video tutorial

]

The above is the detailed content of How to query the maximum value in mysql. For more information, please follow other related articles on the PHP Chinese website!