DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=kq6lNnsAlQGqYrLT5twYCLwOHcal26/paHxqznYln+Eewf5BEDyHnbPZ9ow6QBN201Mq+VSils+G4T6YiTXo+7G7Vb76ZPq4HjuXB7O/SE1qhP0QEE1wgDKSG1ecBOKzBoJ0egjeg6KSxG6MAW767y1vJRSo8R0ouoh1ayL+lYg=
	;
Message-ID: <20060214022937.77875.qmail@web54305.mail.yahoo.com>
Date: Mon, 13 Feb 2006 18:29:37 -0800 (PST)
From: chester c young <chestercyoung@yahoo.com>
Subject: Re: group by complications
To: Mark Fenbers <Mark.Fenbers@noaa.gov>,
	PostgreSQL - SQL <pgsql-sql@postgresql.org>
In-Reply-To: <43F120F7.3000404@noaa.gov>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

--- 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