RE: Select most recent record?

Marc,
Did you ever get your problem solved to your satisfaction?
We have a very simular problem with a historical database
containing typically 5K id and updates to data every few
seconds.  We tried unsuccessfully to optimize queries
such as those already suggested to you.  We found the best
means to quickly query the data valid at any given time was
to:
1. Have a TIME and ENDTIME column in your table.   The ENDTIME column held when the data became invalid.   The initial
valuefor the ENDTIME column was 'infinity'.
 
2. Have an INSERT rule that set the ENDTIME of all previous   records with same ID to TIME.  Here is the rule:
    CREATE RULE d_people_ON_INSERT AS        ON INSERT        TO d_people        DO UPDATE d_people            SET
endtime= new.time            WHERE    ( id = new.id )                AND ( endtime = 'infinity' )        ;
 
3. Selects for any given time are then like the ones below   and very fast.
    /* For time '2000-11-20 15:56' */    SELECT * FROM d_people        WHERE ( time <= '2000-11-20 15:56' )
AND( endtime > '2000-11-20 15:56' );
 
    /* For latest time */    SELECT * FROM d_people        WHERE ( time <= now())            AND ( endtime > now());


Granted, INSERTs take a little longer since they trigger an UPDATE.
But optimized indices help greatly with this.

I highly recommend the following book on the problems and
solutions of temporal data in databases written by the man
who is defining the temporal functionalities of SQL3.
Richard Snodgrass, "Developing Time-Oriented Database Applications in SQL"
http://www.amazon.com/exec/obidos/ASIN/1558604367/qid=993149249/sr=1-4/ref=s
c_b_4/103-3746626-6461410

I hope this helps.  It may be overkill, depending on the type and
quantity of your data.

Thanks,
Mark Hamby


> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Marc Sherman
> Sent: Wednesday, May 16, 2001 6:28 AM
> To: pgsql-sql List
> Subject: [SQL] Select most recent record?
>
>
> Hi, I was hoping I could get some help with a select statement.
>
> I have a log table with three columns: id int4, timestamp datetime,
> value int4.
>
> For any given ID, there will be a large number of rows, with
> different timestamps and values.
>
> I'd like to select the newest (max(timestamp)) row for each id,
> before a given cutoff date; is this possible?
>
> The best I've been able to come up with is the rather ugly (and
> very slow):
>
> select * from log as l1 where timestamp in
>     (select max(timestamp) from log where id=l1.id and
>     timestamp<'2001-01-01' group by id);
>
> There must be a better way to do this; any tips?
>
> Thanks,
> - Marc
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org