MySQL extracts records with the maximum value from a column.

During database usage, we sometimes need to retrieve the record rows with the maximum or minimum values for a column in a table. How can this problem be solved? The following describes several methods.

First, define a table as follows:

This is a simple table. We can use this table to obtain the record row containing the maximum price. What do you want to do?

(1) subquery

I think this method is the first one that you think of. It's good. It's also the first one that I think. Now let's implement it.

Why? Let's take a look at its execution plan:

From the execution plan, we can know that this query is divided into two phases: the first basic query, the full table scan, the second is a subquery, and the full table scan.

(2) sort

You will also say that you can sort the maximum values first, and then retrieve the first row. You are too smart. Now let's implement it.

Let's take a look at the results as you wish. Let's take a look at the execution plan.

From the execution plan, we can see that only one scan process is required to scan the entire table and sort the files.

(3) Use left Outer Join

Can you use other methods in addition to the preceding two operations? If so, it means your level is okay... The following method is described.

We can use the left Outer Join method. The left Outer Join means that each element in the table on the left will have a record in the result set. Let's connect this table to ourselves, then, retrieve the records whose prices in all left tables are smaller than those in the right table, so there will be records for all records except the maximum value, because the maximum value is not greater than the price, therefore, when matching, the matching row in the right table is filled with a null value, we only need to find this null value, the corresponding left table record is the record line containing the maximum price. Now implement the following:

From the results, we can see that, except for the last row, the records in the S2 table of other rows are not empty, meaning that the records in the S1 table of the corresponding row are not the largest row of records. Only the S1 table row corresponding to the row with null records in Table S2 is the maximum record row corresponding to table S1. Therefore, we only need to add where s2.article is null in the conditions of the preceding query.

Let's take a look at its execution plan:

Now, we have finished the three methods. You can choose the right one to use in the future ....