Skip site navigation (1) Skip section navigation (2)

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

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: Dave Crooke <dcrooke(at)gmail(dot)com>, pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Picking out the most recent row using a time stamp column
Date: 2011-02-24 21:14:59
Message-ID: AANLkTinmw55=b0ESxgwtPjjMCk=Kz9XBVPTBZMbKmmNw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Dave CrookeDate: 2011-02-24 23:38:37
Subject: Re: Picking out the most recent row using a time stamp column
Previous:From: Dave JohansenDate: 2011-02-24 20:51:53
Subject: Re: Pushing IN (subquery) down through UNION ALL?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group