pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael J Schout <mschout@mail.gkg-com.com>
To: pgsql-sql@postgresql.org
Subject: Grouping question.
Date: Tue, 26 May 1998 23:45:23 -0500 (CDT)
Message-ID: <199805270445.XAA23195@mail.gkg-com.com> (raw)

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



Message-ID: <199805270445.XAA23195@mail.gkg-com.com>
Permalink:  ../199805270445.XAA23195@mail.gkg-com.com/
Also on:    postgresql.org/message-id/199805270445.XAA23195@mail.gkg-com.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: mschout@mail.gkg-com.com
  Subject: Re: Grouping question.
  In-Reply-To: <199805270445.XAA23195@mail.gkg-com.com>
  References: <199805270445.XAA23195@mail.gkg-com.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox