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;