X-Original-To: pgsql-sql-postgresql.org@postgresql.org
Received: from localhost (wm.hub.org [200.46.204.128])
	by postgresql.org (Postfix) with ESMTP id 81E529FD108
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 28 Aug 2006 15:10:09 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024)
	with ESMTP id 46652-06 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 28 Aug 2006 18:10:07 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from nf-out-0910.google.com (nf-out-0910.google.com
 [64.233.182.191])
	by postgresql.org (Postfix) with ESMTP id 97CF89FD105
	for <pgsql-sql@postgresql.org>; Mon, 28 Aug 2006 15:10:06 -0300 (ADT)
Received: by nf-out-0910.google.com with SMTP id p77so7433nfc
	for <pgsql-sql@postgresql.org>; Mon, 28 Aug 2006 11:10:03 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:mime-version:content-type;
	b=VgkLh4wj6rK3SeWrCRxmkSzOvafBM/5KcWgDW7ExCXzhGYVJ7zcmh7VErSI3qssnhyFcDZtaGacxv7g9WKkZbdvYlHcA9z6lslQEK1MoA2GNWMMVhCpN/Mf8lGv1f8H+wrwEm/zdM1CGQJmyt+9DtSfZk6xBMatT8iPvN6jH9k0=
Received: by 10.49.55.13 with SMTP id h13mr9334008nfk;
	Mon, 28 Aug 2006 11:10:03 -0700 (PDT)
Received: by 10.48.243.12 with HTTP; Mon, 28 Aug 2006 11:10:02 -0700 (PDT)
Message-ID: <2bffcc330608281110j404e3fd1q9d6d27a6a97ad31d@mail.gmail.com>
Date: Mon, 28 Aug 2006 14:10:02 -0400
From: "Henry Ortega" <juandelacruz@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Fastest way to get max tstamp
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_51873_9054109.1156788602881"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.696 tagged_above=0 required=5
	tests=DNS_FROM_RFC_ABUSE, HTML_40_50, HTML_MESSAGE, SPF_PASS
X-Spam-Level: 
X-Archive-Number: 200608/224
X-Sequence-Number: 26182

------=_Part_51873_9054109.1156788602881
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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?

------=_Part_51873_9054109.1156788602881
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

This is my table:<br>&nbsp;name | program | effective&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tstamp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | rate <br>------+---------+------------+----------------------------+------<br>&nbsp;jdoe | AAA&nbsp;&nbsp;&nbsp;&nbsp; | 2006-07-01 | 2006-07-16 23:42:13.809214 |&nbsp;&nbsp; 20
<br>&nbsp;jdoe | BBB&nbsp;&nbsp;&nbsp;&nbsp; | 2006-07-01 | 2006-07-16 23:42:13.809214 |&nbsp;&nbsp; 20<br>&nbsp;jdoe | AAA&nbsp;&nbsp;&nbsp;&nbsp; | 2006-08-16 | 2006-08-25 11:56:50.380575 |&nbsp;&nbsp; 20<br>&nbsp;jdoe | BBB&nbsp;&nbsp;&nbsp;&nbsp; | 2006-08-16 | 2006-08-25 11:56:50.380575 |&nbsp;&nbsp; 20<br>&nbsp;jdoe | AAA&nbsp;&nbsp;&nbsp;&nbsp; | 2006-08-16 | 2006-08-25 11:57:
17.394854 |&nbsp;&nbsp; 20<br>&nbsp;jdoe | BBB&nbsp;&nbsp;&nbsp;&nbsp; | 2006-08-16 | 2006-08-25 11:57:17.394854 |&nbsp;&nbsp; 20<br>&nbsp;jdoe | AAA&nbsp;&nbsp;&nbsp;&nbsp; | 2006-09-01 | 2006-08-28 12:38:42.486513 |&nbsp;&nbsp; 20<br>&nbsp;jdoe | BBB&nbsp;&nbsp;&nbsp;&nbsp; | 2006-09-01 | 2006-08-28 12:38:42.486513 |&nbsp;&nbsp; 20<br>
<br><span style="font-weight: bold;">I want to get:</span><br>&nbsp;name | program | effective&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tstamp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | rate <br>------+---------+------------+----------------------------+------<br>&nbsp;jdoe | AAA&nbsp;&nbsp;&nbsp;&nbsp; | 2006-07-01 | 2006-07-16 23:42:
13.809214 |&nbsp;&nbsp; 20<br>&nbsp;jdoe | BBB&nbsp;&nbsp;&nbsp;&nbsp; | 2006-07-01 | 2006-07-16 23:42:13.809214 |&nbsp;&nbsp; 20<br>&nbsp;jdoe | AAA&nbsp;&nbsp;&nbsp;&nbsp; | 2006-08-16 | 2006-08-25 11:57:17.394854 |&nbsp;&nbsp; 20<br>&nbsp;jdoe | BBB&nbsp;&nbsp;&nbsp;&nbsp; | 2006-08-16 | 2006-08-25 11:57:17.394854 |&nbsp;&nbsp; 20<br>
&nbsp;jdoe | AAA&nbsp;&nbsp;&nbsp;&nbsp; | 2006-09-01 | 2006-08-28 12:38:42.486513 |&nbsp;&nbsp; 20<br>&nbsp;jdoe | BBB&nbsp;&nbsp;&nbsp;&nbsp; | 2006-09-01 | 2006-08-28 12:38:42.486513 |&nbsp;&nbsp; 20<br><br>Basically, for effective='08-16-2006', it only gets the latest inserted<br>record (using tstamp) for that effective date, which is 2006-08-25 11:57:
17.394854.<br><br>So what is the quickest way to do this?<br><span style="font-weight: bold;">I can always do:</span><br>Select * from Table t where tstamp=(select max(tstamp) from Table t2 where t2.name=t.name and t2.effective=t.effective
)<br>but it takes so long since this is a huge table.<br><br>Any suggestions?<br>

------=_Part_51873_9054109.1156788602881--