On Thu, Feb 24, 2011 at 2:18 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> 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)
hm. not only is it faster, but much more flexible...that's definitely
the way to go.
merlin