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

On Thu, Feb 24, 2011 at 2:18 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> 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)

hm. not only is it faster, but much more flexible...that's definitely
the way to go.

merlin