group by complications

From: Mark Fenbers <Mark(dot)Fenbers(at)noaa(dot)gov>
To: PostgreSQL - SQL <pgsql-sql(at)postgresql(dot)org>
Subject: group by complications
Date: 2006-02-14 00:14:47
Message-ID: 43F120F7.3000404@noaa.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Ken Hill 2006-02-14 01:48:45 Slow update SQL
Previous Message Mario Splivalo 2006-02-13 21:28:38 Re: ORDER BY CASE ...