How to query the maximum id value in mysql

In mysql, you can use the SELECT statement to query all the data in the specified table, and then use the max() function to return the maximum value of the id column in the query result set. The query syntax "SELECT MAX(id) FROM table name;". The max() function is used to return the maximum value in the specified column in the query result set, the syntax is "MAX (column name)"; and if you want to obtain the maximum value of the id column, the parameter value in the max() function needs to be set to id.

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

In mysql, you can use the SELECT statement with the max() function to query the maximum id value.

• First use the SELECT statement to query all the data in the specified table

• Then use the max() function to return the id column in the query result set The maximum value is

SELECT MAX(列名) FROM 表名;

MAX() function is used to return the maximum value in the specified column. The accepted parameter is the specified column name; therefore, if you want to query the maximum id value, just Get the maximum value of the id column.

So the complete syntax for querying the maximum id value is:

SELECT MAX(id) FROM 表名;

Example:

Create a tb_students_score table, insert the data, and query all data

SELECT * FROM tb_students_score;

Query the maximum id value:

SELECT MAX(id) FROM tb_students_score;

Extended knowledge:

MAX() 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.

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 different values, which is the same as that of all values The maximum values ​​are the same. This means that the DISTINCT operator will not have any impact on the MAX() function (it can be used with or without the DISTINCT operator).

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

• MySQL MAX function in subquery

Not only to get the maximum payment amount, but also to get other payment information, such as customer number, For check number and payment date, you can use the MAX function in a subquery, as shown below:

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? The subquery returns all maximum payment amounts. The outer query retrieves other related payment information for an amount equal to the maximum amount returned from the subquery. Another method without using the MAX function is to use the ORDER BY clause to sort the result set in descending order, and use the LIMIT clause to get the first row, as in the following query:

SELECT 
    *
FROM
    payments
ORDER BY amount DESC
LIMIT 1;

Execute the above query statement and get the following Result

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

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

[Related recommendations: mysql video tutorial]

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