How to find the latest data row that meets the conditions in MySql

Combined example:

This is a record sheet recording people’s visits.
The data in the data table accurately records the color of the hat that each person wore when visiting, the time, and the person code (unique for each person).

Data sample:

What needs to be done is:

Come up with the latest data that meets the conditions Visit records.

What would you do best?

First implement one thing, take out the latest visit record of the person code A101.

First show the wrong sql example: taking the max() function for granted.

SELECT MAX(id) AS id ,user_code,cap_color,create_time FROM vist_record WHERE user_code='A101' ;

Query results (wrong results) ：

Obviously the data appears to be correct at first sight, but it is actually wrong.

Why is it wrong? You can tell me a little bit, since some people are interested in the comment area (brothers are welcome to express their opinions).

Simple description, max is an aggregate function. Our error example is not used with group by. In fact, only mysql can let us execute it at this time, and many databases directly report errors.

Then the execution is executed. In fact, at this time, MySQL is equivalent to treating the entire table as a content block to perform a compressed retrieval.

We added the where condition user_code='A101', so the entire content block does filter out other data that is not user_code='A101'.

In other words, under this kind of lax execution, mysql guarantees that max returns the maximum value (relevant column), but it is not guaranteed for other column fields.

The correct data is:

Is max(id) unusable?

Correct usage (use the maximum id value that meets the conditions as a condition):

SELECT
id,user_code,cap_color,create_time
FROM vist_record
WHERE id IN (SELECT MAX(id) AS id FROM vist_record WHERE user_code='A101' )

Query results:

But I saw the above use With this method of subquery,

everyone must have been secretly cursing, is it so troublesome to get the latest data?

Is there anything simpler?

have.

For example, we have determined that the id is auto-incrementing, and the data with the largest id (data that meets the conditions) is the latest.

Then we can use reverse order DESC to get the latest data:

DESC is reverse order/descending order.

PS:

Use reverse search:

SELECT *
FROM vist_record
WHERE user_code='A101'
ORDER BY id DESC
LIMIT 1;

Query results:

Or in reverse order according to time:

SELECT *
FROM vist_record
WHERE user_code='A101'
ORDER BY create_time DESC
LIMIT 1;

Query results:

Is it that simple to achieve?

So what if what we need is not to specify A101 but the latest data of everyone involved?

That is, there are multiple groups of concepts.

The latest data that meets the conditions for each category

The orange boxes are the latest records of A101, B202, and C303, which we want to take out.

Error example:

SELECT MAX(id) AS id ,user_code,cap_color,create_time FROM vist_record GROUP BY user_code

Wrong filter result:

Correct encoding:

SELECT  id ,user_code,cap_color,create_time FROM vist_record  WHERE id in
(
SELECT MAX(id) AS id  FROM vist_record  GROUP BY user_code
)

The above is the detailed content of How to find the latest data row that meets the conditions in MySql. For more information, please follow other related articles on the PHP Chinese website!