0

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.

Björn C
  • 3,860
  • 10
  • 46
  • 85

2 Answers2

3

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
GMB
  • 216,147
  • 25
  • 84
  • 135
0

You can use the NOT EXISTS as follows:

select *
from element_location e
where not exists (
    select 1
    from element_location ee
    where ee.obj_nr = e.obj_nr and ee.element_nr = e.element_nr
      and ee.date > e.date
)
Popeye
  • 35,427
  • 4
  • 10
  • 31