--- Mark Fenbers <[EMAIL PROTECTED]> wrote: > 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; >
use your original query as part of the from clause, then add columns to it through a subquery or a join. try something like this: select q1.*, (select obsvalue from height where lid=q1.lid and obstime=q1.obstime) as obsvalue from (select l.lid,l.fs,max(h.obstime) as obstime1 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 ) q1; __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings