From: | Michael J Schout <mschout(at)mail(dot)gkg-com(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Grouping question. |
Date: | 1998-05-27 04:45:23 |
Message-ID: | 199805270445.XAA23195@mail.gkg-com.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Przemyslaw Bak | 1998-05-27 06:22:09 | |
Previous Message | Christian Guenther | 1998-05-26 18:49:19 | how to compile and use soundex |