Select most recent record?

From: "Marc Sherman" <msherman(at)projectile(dot)ca>
To: "pgsql-sql List" <pgsql-sql(at)postgresql(dot)org>
Subject: Select most recent record?
Date: 2001-05-16 13:28:29
Message-ID: CGEPKMKAIFJINAOACFFEOEJDCGAA.msherman@projectile.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Srikanth Rao 2001-05-16 13:34:54 Restricting the number of decimal digits
Previous Message Chris Ryan 2001-05-16 12:45:36 [Fwd: [Gborg-bugs] BUG: reference error when using inherited tables (ID: 269) (new)]