On Thu, Feb 24, 2011 at 2:18 PM, Kevin Grittner <Kevin.Grittner@xxxxxxxxxxxx> wrote: > 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) hm. not only is it faster, but much more flexible...that's definitely the way to go. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
