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?

Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) — , Apr 07 '20 at 11:54

score 1 · Accepted Answer · answered Apr 07 '20 at 11:56

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;

answered Apr 07 '20 at 11:56

Yogesh Sharma

49,870
5
26
52

score 0 · Answer 2 · answered Apr 07 '20 at 11:56

Use ROW_NUMBER combined with an aggregation:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE DESC) rn
    FROM yourTable
)

SELECT ID, MAX(M1) AS M1, MAX(M2) AS M2
FROM cte
WHERE rn = 1
GROUP BY ID;

The row number lets us restrict to only records for each ID having the most recent year date. Then, we aggregate to find the max values for M1 and M2.

score 0 · Answer 3 · answered Apr 07 '20 at 11:56

In standard SQL, you can use lag(ignore nulls):

select id, coalesce(m1, prev_m1), coalesce(m2, prev_m2)
from (select t.*, 
             lag(m1 ignore nulls) over (partition by id order by date) as prev_m1,
            lag(m2 ignore nulls) over (partition by id order by date) as prev_m2,
             row_number() over (partition by id order by date desc) as seqnum
      from t
     ) t
where seqnum = 1;

Get most recent measurement

3 Answers3