Get child value based on timestamp for each parent

Viewed 48

I have these 2 tables in SQL Server 2012 parent-child_tables

For each parent I would like to retrieve value based on the most recent timestamp.

This is what I've got so far by doing some searching and trial and error.

select m.id, m.Name, mmi.previousValue
from [ManualMeters] m
inner join 
(
    select mm.ManualMeterId, max(mm.Value) previousValue
    from ManualMetersInput mm 
    --where mm.Timestamp = max(mm.Timestamp)
    group by mm.ManualMeterId
        having count(*) > 1
) mmi on (mmi.ManualMeterId = m.Id )

So I would expect here to see:

ID    Name       previousValue   TimeStamp
1     Meter_1    5000            2016-03-19 12:00
2     Meter_2    3500            2016-03-18 12:15

Now it only shows max values but that isn't what I need. I need the value from the most recent timestamp.

2 Answers
Related