Date: Mon, 13 Feb 2006 19:14:47 -0500
From: Mark Fenbers <Mark.Fenbers@noaa.gov>
Subject: group by complications
To: PostgreSQL - SQL <pgsql-sql@postgresql.org>
Message-id: <43F120F7.3000404@noaa.gov>
Organization: DOC/NOAA/NWS/OHRFC
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7BIT
User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)

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;

Can someone offer hints, please?

Mark