select l.lid,l.fs,max(h.obstime) from location as l
inner join height as h on h.lid = l.lid
where l.fs > 0.0
group by l.lid,l.fs;
The above query works as expected in that is fetches the lid, fs and
time of the latest observation in the height table (for the
corresponding lid), but I also want to fetch (i.e., add to the select
list) the corresponding reading (h.obsvalue) which occurs at
max(h.obstime). I'm having trouble formulating the correct SQL syntax
to pull out the l.lid, l.fs, and the most recent h.obvalue (with or
without the time that it occurred).
Logistically, I want to do something like this:
select l.lid,l.fs,most_recent(h.obsvalue) from location as l
inner join height as h on h.lid = l.lid
where l.fs > 0.0
group by l.lid,l.fs;
Can someone offer hints, please?
Mark