Skip to content

Find rows that have the same value and select the newer one

I got a table, that looks like this:

serialNr sensorNr ModifyDate
1234 12EE56423 2022-04-06
4567 12EE56423 2018-06-12
6789 AD3FF0C44 2018-03-08
9101 AD3FF0C44 2019-06-07

From rows with the same sensorNr, I only want to select those with newer ModifyDate, so the result should look like this:

serialNr sensorNr ModifyDate
1234 12EE56423 2022-04-06
9101 AD3FF0C44 2019-06-07

How can I achieve that?

Answer

On MySQL 8+, we can use ROW_NUMBER here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY sensorNr ORDER BY ModifyDate DESC) rn
    FROM yourTable
)

SELECT serialNr, sensorNr, ModifyDate
FROM cte
WHERE rn = 1;