From: | "Henry Ortega" <juandelacruz(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Fastest way to get max tstamp |
Date: | 2006-08-28 18:10:02 |
Message-ID: | 2bffcc330608281110j404e3fd1q9d6d27a6a97ad31d@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
This is my table:
name | program | effective | tstamp | rate
------+---------+------------+----------------------------+------
jdoe | AAA | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20
jdoe | BBB | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20
jdoe | AAA | 2006-08-16 | 2006-08-25 11:56:50.380575 | 20
jdoe | BBB | 2006-08-16 | 2006-08-25 11:56:50.380575 | 20
jdoe | AAA | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20
jdoe | BBB | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20
jdoe | AAA | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20
jdoe | BBB | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20
I want to get:
name | program | effective | tstamp | rate
------+---------+------------+----------------------------+------
jdoe | AAA | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20
jdoe | BBB | 2006-07-01 | 2006-07-16 23:42:13.809214 | 20
jdoe | AAA | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20
jdoe | BBB | 2006-08-16 | 2006-08-25 11:57:17.394854 | 20
jdoe | AAA | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20
jdoe | BBB | 2006-09-01 | 2006-08-28 12:38:42.486513 | 20
Basically, for effective='08-16-2006', it only gets the latest inserted
record (using tstamp) for that effective date, which is 2006-08-25 11:57:
17.394854.
So what is the quickest way to do this?
I can always do:
Select * from Table t where tstamp=(select max(tstamp) from Table t2 where
t2.name=t.name and t2.effective=t.effective)
but it takes so long since this is a huge table.
Any suggestions?
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Broersma Jr | 2006-08-28 19:23:07 | Re: Fastest way to get max tstamp |
Previous Message | Henry Ortega | 2006-08-28 15:53:36 | Re: Trigger on Insert to Update only newly inserted fields? |