Home > Database > Mysql Tutorial > body text

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

PHPz
Release: 2023-05-27 11:31:12
forward
1605 people have browsed it

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).
How to find the latest data row that meets the conditions in MySql

Data sample:

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

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) :

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

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:

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

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:
How to find the latest data row that meets the conditions in MySql

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:
How to find the latest data row that meets the conditions in MySql

Use reverse search:

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

Query results:

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

Or in reverse order according to time:

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

Query results:

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

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.

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

Error example:

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

Wrong filter result:

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

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
)

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

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!

Related labels:
source:yisu.com
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!