Re: Picking out the most recent row using a time stamp column - Mailing list pgsql-performance

From Kevin Grittner
Subject Re: Picking out the most recent row using a time stamp column
Date
Msg-id 4D6668CF020000250003AFC9@gw.wicourts.gov
Whole thread Raw
In response to Picking out the most recent row using a time stamp column  (Dave Crooke <dcrooke@gmail.com>)
Responses Re: Picking out the most recent row using a time stamp column  (Merlin Moncure <mmoncure@gmail.com>)
Re: Picking out the most recent row using a time stamp column  (Florian Weimer <fw@deneb.enyo.de>)
List pgsql-performance
Dave Crooke <dcrooke@gmail.com> wrote:

> create table data
>    (id_key int,
>     time_stamp timestamp without time zone,
>     value double precision);
>
> create unique index data_idx on data (id_key, time_stamp);

> I need to find the most recent value for each distinct value of
> id_key.

Well, unless you use timestamp WITH time zone, you might not be able
to do that at all.  There are very few places where timestamp
WITHOUT time zone actually makes sense.

> There is no elegant (that I know of) syntax for this

How about this?:

select distinct on (id_key) * from data order by id_key, time_stamp;

> select
>    a.id_key, a.time_stamp, a.value
> from
>    data a
> where
>   a.time_stamp=
>      (select max(time_stamp)
>       from data b
>       where a.id_key=b.id_key)

Rather than the above, I typically find this much faster:

select
   a.id_key, a.time_stamp, a.value
from
   data a
where not exists
  (select * from data b
   where b.id_key=a.id_key and b.time_stamp > a.time_stamp)

-Kevin

pgsql-performance by date:

Previous
From: Merlin Moncure
Date:
Subject: Re: Picking out the most recent row using a time stamp column
Next
From: Michael Glaesemann
Date:
Subject: Re: Picking out the most recent row using a time stamp column