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?
Advertisement
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;