Get most recent measurement

Question

I have a table that has has some measurements, ID and date.

The table is built like so

ID     DATE    M1    M2
1      2020     1    NULL
1      2020    NULL   15
1      2018     2    NULL
2      2019     1    NULL
2      2019    NULL   1

I would like to end up with a table that has one row per ID with the most recent measurement

ID M1 M2
1  1  15
2  1   1

Any ideas?

Answer 1

You can use correlated sub-query with aggregation :

select id, max(m1), max(m2)
from t 
where t.date = (select max(t1.date) from t t1 where t1.id = t.id)
group by id;