Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 364119FBFD4
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 16 Mar 2007 12:55:50 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024)
	with ESMTP id 12152-08 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 16 Mar 2007 12:55:36 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.174])
	by postgresql.org (Postfix) with ESMTP id 491CF9FBF5D
	for <pgsql-sql@postgresql.org>; Fri, 16 Mar 2007 12:55:37 -0300 (ADT)
Received: by ug-out-1314.google.com with SMTP id k3so649972ugf
	for <pgsql-sql@postgresql.org>; Fri, 16 Mar 2007 08:55:34 -0700 (PDT)
DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=OnKZE5RgCu6ZWbZcug/jaJCtqJX6GJsByECHSZkOiHsAJGhzdVdkOLBKUvwbGhvh6royCbgbjpaV1XlU4hdBh0fU8+pnwh2g4ZS++7iEhrZwF6/H9WrdO0xW/WHPhh64lVLhQpby03n1iL91bJ5udAb+1ZsL9qv9W3pZgDdKK2E=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=Fq9PJ0mAuItOq4URSUb8Alz6iPWTH+ogIKfFAK9yHoak1lh2teiWVplnQOA4hWuEqBi4ylcYSDdCRFdiUhA1oOo0/w7gIxx8Mcf3nAgmtqA4S5LR19KzQkIcJHb7O0dx88XjAyefS82ApgduP86oX0u0o4xBJW+0GsfCuiKwmF8=
Received: by 10.115.19.16 with SMTP id w16mr12942wai.1174060533779;
	Fri, 16 Mar 2007 08:55:33 -0700 (PDT)
Received: by 10.114.171.9 with HTTP; Fri, 16 Mar 2007 08:55:33 -0700 (PDT)
Message-ID: <cf9b4f3e0703160855q537eafe6yf3c3bbea090e5b6e@mail.gmail.com>
Date: Fri, 16 Mar 2007 10:55:33 -0500
From: "Travis Whitton" <tinymountain@gmail.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: Grabbing Newest Records From Duplicates
Cc: pgsql-sql@postgresql.org
In-Reply-To: <7240.1174059340@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_97550_1509553.1174060533728"
References: <cf9b4f3e0703160828s2cc0b9v6c01db6e8634e37e@mail.gmail.com>
	<7240.1174059340@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200703/230
X-Sequence-Number: 27868

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

One last question, using the "weather report" example, is it going to
improve performance for the DISTINCT ON query to add an index to the
location and time columns?

Thanks a lot,
Travis

On 3/16/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Travis Whitton" <tinymountain@gmail.com> writes:
> > Given the following test table, I want to grab only the newest record
> and
> > disregard any older duplicates based on name. Is this the most efficient
> way
> > to do it?
>
> No, it's gonna be pretty awful.  The best way I know of involves
> DISTINCT ON (see the "weather reports" example in the SELECT reference
> page).  Unfortunately that's a Postgres-only construct.  If you want
> something portable then you'll need something messy with subqueries...
>
>                         regards, tom lane
>

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

One last question, using the &quot;weather report&quot; example, is it going to improve performance for the DISTINCT ON query to add an index to the location and time columns?<br><br>Thanks a lot,<br>Travis<br><br><div><span class="gmail_quote">
On 3/16/07, <b class="gmail_sendername">Tom Lane</b> &lt;<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
&quot;Travis Whitton&quot; &lt;<a href="mailto:tinymountain@gmail.com">tinymountain@gmail.com</a>&gt; writes:<br>&gt; Given the following test table, I want to grab only the newest record and<br>&gt; disregard any older duplicates based on name. Is this the most efficient way
<br>&gt; to do it?<br><br>No, it&#39;s gonna be pretty awful.&nbsp;&nbsp;The best way I know of involves<br>DISTINCT ON (see the &quot;weather reports&quot; example in the SELECT reference<br>page).&nbsp;&nbsp;Unfortunately that&#39;s a Postgres-only construct.&nbsp;&nbsp;If you want
<br>something portable then you&#39;ll need something messy with subqueries...<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;regards, tom lane<br></blockquote></div><br>

------=_Part_97550_1509553.1174060533728--