Скачать или смотреть How to Select Rows with the Newest Modification Dates in MySQL

Discover how to filter database rows to get the most recent entries based on unique values. Learn a straightforward method using MySQL for efficient data handling.
---
This video is based on the question https://stackoverflow.com/q/72345358/ asked by the user 'drummercrm' ( https://stackoverflow.com/u/1619655/ ) and on the answer https://stackoverflow.com/a/72345421/ provided by the user 'Tim Biegeleisen' ( https://stackoverflow.com/u/1863229/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Find rows that have the same value and select the newer one

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Select Rows with the Newest Modification Dates in MySQL

When working with databases, it may often be necessary to retrieve entries that hold certain common characteristics but are updated at different times. In many scenarios, you might only want to focus on the latest updates—this can be especially critical in applications that track changes over time, like sensor readings.

Problem Statement

Suppose you have a database table containing sensor information recorded over different periods. Below is an example of such a table:

serialNrsensorNrModifyDate123412EE564232022-04-06456712EE564232018-06-126789AD3FF0C442018-03-089101AD3FF0C442019-06-07From this table, we want to obtain the rows with the same sensorNr but only include the one that has the newest ModifyDate. The expected output would look like this:

serialNrsensorNrModifyDate123412EE564232022-04-069101AD3FF0C442019-06-07So, how can we achieve this efficiently in MySQL?

Solution

In MySQL 8 and above, you can use the ROW_NUMBER() window function, which makes this kind of task manageable. Here’s a step-by-step explanation of how to implement this:

Step-by-Step Approach

Common Table Expression (CTE): We will first create a CTE that assigns a row number to each entry based on its ModifyDate. This row number will reset with each unique sensorNr.

Using ROW_NUMBER(): This function will help us organize the rows in descending order of their ModifyDate.

Filter for the Latest Row: Finally, we’ll select from the CTE where the row number equals 1, giving us the most recent entries.

SQL Query to Use

Here is the SQL code you would execute:

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the SQL

CTE Declaration: The WITH cte AS (...) part initializes a CTE where we generate new data based on our existing table (yourTable).

Selecting with ROW_NUMBER():

PARTITION BY sensorNr: This specifies that we’re interested in grouping the data by sensorNr.

ORDER BY ModifyDate DESC: This orders the grouped data, allowing us to assign row numbers starting from the most recent date.

Final Selection: We then simply retrieve the rows where rn = 1, which represent the latest modification date for each sensor.

Conclusion

Using the above SQL query, you can efficiently filter your data, ensuring that you only retain the entries with the newest modification dates per unique sensor. This is especially useful in contexts where data accuracy and recency are crucial.

Feel free to experiment with this SQL pattern on your database, and tailor it as needed for your specific use case!