X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 6B0C89FA12F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  1 Jun 2006 22:44:37 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 16778-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  1 Jun 2006 22:44:24 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.174])
	by postgresql.org (Postfix) with ESMTP id 3F6CA9FA040
	for <pgsql-sql@postgresql.org>; Thu,  1 Jun 2006 22:44:22 -0300 (ADT)
Received: by ug-out-1314.google.com with SMTP id a2so311914ugf
	for <pgsql-sql@postgresql.org>; Thu, 01 Jun 2006 18:44:21 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth;
	b=hIDhnrCMZ0QeBYEGngz+UhfnViboRvh9UIkJ1uKHJ/FqmqZQKp2+grUj2DhjyAtrIrzb8EGG+/fenoJw7LTc6sMyaJz2WtJ9QSYDJhtkCGnVflQmwa15fFhOqAZjNBePXUAj2SX0pvJukRURc4/yFtOqG0lZ+3ST7oy9RrfSocA=
Received: by 10.67.15.3 with SMTP id s3mr303515ugi;
	Thu, 01 Jun 2006 18:44:18 -0700 (PDT)
Received: by 10.67.20.12 with HTTP; Thu, 1 Jun 2006 18:44:18 -0700 (PDT)
Message-ID: <bf05e51c0606011844s5553241ev144934da7e52f5f0@mail.gmail.com>
Date: Thu, 1 Jun 2006 20:44:18 -0500
From: "Aaron Bono" <postgresql@aranya.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: Am I crazy or is this SQL not possible
Cc: pgsql-sql@postgresql.org
In-Reply-To: <14505.1149189691@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_71_19721805.1149212658860"
References: <df01c91b0606011043g61328d6habaee8fa037a5ccf@mail.gmail.com>
	<14505.1149189691@sss.pgh.pa.us>
X-Google-Sender-Auth: 908687ea60b9301d
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200606/35
X-Sequence-Number: 25271

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

Is this SQL-99 compliant or a PostgreSQL specific query?  I really like it
and have never seen this before.

-Aaron

On 6/1/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Collin Peters" <cadiolis@gmail.com> writes:
> > What I would like to do is simply get the last date_sent and it's
> > status for every broadcast.  I can't do a GROUP BY because I can't put
> > an aggregate on the status column.
>
> You missed the key idea about how to use DISTINCT ON.
>
> SELECT DISTINCT ON (email_broadcast_id) *
> FROM email_broadcast_history
> ORDER BY email_broadcast_id, date_sent DESC
>
> You order by the DISTINCT ON fields, then one or more additional fields
> to select the representative row you want within each DISTINCT ON group.

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

Is this SQL-99 compliant or a PostgreSQL specific query?&nbsp; I really like it and have never seen this before.<br><br>-Aaron<br><br><div><span class="gmail_quote">On 6/1/06, <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;Collin Peters&quot; &lt;<a href="mailto:cadiolis@gmail.com">
cadiolis@gmail.com</a>&gt; writes:<br>&gt; What I would like to do is simply get the last date_sent and it's<br>&gt; status for every broadcast.&nbsp;&nbsp;I can't do a GROUP BY because I can't put<br>&gt; an aggregate on the status column.
<br><br>You missed the key idea about how to use DISTINCT ON.<br><br>SELECT DISTINCT ON (email_broadcast_id) *<br>FROM email_broadcast_history<br>ORDER BY email_broadcast_id, date_sent DESC<br><br>You order by the DISTINCT ON fields, then one or more additional fields
<br>to select the representative row you want within each DISTINCT ON group.</blockquote></div>

------=_Part_71_19721805.1149212658860--