mySQL How to SELECT MAX(date) from duplicate values without group

Question

I have this table:

id   obj_nr   el_nr   location   date
0    1234     1        a3         2020-01-01
1    1234     2        a2         2020-01-02
2    1234     3        a4         2020-01-03
3    1234     1        a9         2020-01-04

No i want to concat obj_nr and element_nr to find duplicates.
When i find a duplicate i wan't only to select the one with the latest date.

The result should be:

id   obj_nr   el_nr   location   date
1    1234     2        a2         2020-01-02
2    1234     3        a4         2020-01-03
3    1234     1        a9         2020-01-04

How should my query look like?

This is what i've tried:

SELECT MAX(id) id, obj_nr, el_nr, location, max(date_scanned)
FROM  element_location WHERE obj_nr = :obj_nr
GROUP BY obj_nr, el_nr, location

But this will not give me the latest duplicate.

Answer 1

You can filter with a subquery:

select *
from element_location el
where date = (
    select max(el1.date)
    from element_location el1
    where el1.obj_nr = el.obj_nr and el1.element_nr = el.element_nr
)

This would take advantage of an index on (obj_nr, element_nr, date desc).

Or, in MySQL 8.0, you can use window functions:

select *
from (
    select el.*, 
        rank() over(partition by obj_nr, element_nr order by date desc) rn
    from element_location el
) el
where rn = 1