Re: group by complications

--- Mark Fenbers <Mark(dot)Fenbers(at)noaa(dot)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