Re: group by complications

--- Mark Fenbers <Mark.Fenbers@noaa.gov> 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