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

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?

CodePudding user response：

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;

CodePudding user response：

You can

SELECT
  serialNr, sensorNr, ModifyDate
FROM
  your_table_name_goes_here
GROUP BY
  sensorNr
ORDER BY
  ModifyDate DESC

it will give you one row for each unique sensorNr and ordering by ModifyDate will make sure that the values for ModifyDate and serialNr will be the "newest"

db-fiddle