From: | chester c young <chestercyoung(at)yahoo(dot)com> |
---|---|
To: | Mark Fenbers <Mark(dot)Fenbers(at)noaa(dot)gov>, PostgreSQL - SQL <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: group by complications |
Date: | 2006-02-14 02:29:37 |
Message-ID: | 20060214022937.77875.qmail@web54305.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
--- 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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2006-02-14 05:17:21 | Re: Slow update SQL |
Previous Message | Michael Fuhr | 2006-02-14 02:14:34 | Re: Slow update SQL |