Received: from mail.gkg-com.com (mschout@mail.gkg-com.com [206.28.9.3]) by hub.org (8.8.8/8.7.5) with ESMTP id AAA08093 for ; Wed, 27 May 1998 00:50:09 -0400 (EDT) Received: (from mschout@localhost) by mail.gkg-com.com (8.8.5/8.8.5) id XAA23195 for pgsql-sql@postgresql.org; Tue, 26 May 1998 23:45:24 -0500 From: Michael J Schout Message-Id: <199805270445.XAA23195@mail.gkg-com.com> Subject: Grouping question. To: pgsql-sql@postgresql.org Date: Tue, 26 May 1998 23:45:23 -0500 (CDT) X-Mailer: ELM [version 2.4ME+ PL37 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Hi. I have a problem doing a query that I think should be simple, but I dont see the simple answer here. The table looks like this: CREATE TABLE mdata ( ts timestamp default now(), city text, data float ); What I want to get is the most up to date "data" for each city (IE the data for each city whose timestamp is the highest). I can get the highest timestamp, and city name with: select max(datetime(ts)), city from mdata group by city; but what I really want is: select max(datetime(ts)), city, data from mdata group by city, max(datetime(ts)); (ie I want the data field too, not just the city names). Does anyone know of a simple solution to this, or even a complex one? The only other think I can think of to do is select the max ts, city, then do a query for each row of that result (yuck!!). Thanks Mike