Dave Crooke <dcrooke(at)gmail(dot)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