Dave Crooke <dcrooke@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
