Re: Picking out the most recent row using a time stamp column

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