From: chester c young <chestercyoung@yahoo.com>
To: Mark Fenbers <Mark.Fenbers@noaa.gov>
To: PostgreSQL - SQL <pgsql-sql@postgresql.org>
Subject: Re: group by complications
Date: Mon, 13 Feb 2006 18:29:37 -0800 (PST)
Message-ID: <20060214022937.77875.qmail@web54305.mail.yahoo.com> (raw)
In-Reply-To: <43F120F7.3000404@noaa.gov>
--- 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: chestercyoung@yahoo.com, Mark.Fenbers@noaa.gov
Subject: Re: group by complications
In-Reply-To: <20060214022937.77875.qmail@web54305.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox