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 9EDB69FA040 for ; Thu, 1 Jun 2006 15:06:00 -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 44124-01 for ; Thu, 1 Jun 2006 15:05:54 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.173]) by postgresql.org (Postfix) with ESMTP id 9BA949FA5C8 for ; Thu, 1 Jun 2006 15:05:54 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id a2so189919ugf for ; Thu, 01 Jun 2006 11:05:53 -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=Gy8fZq60jy3iAMM+rrVt+e+K61Pzt9gmVPucCOsNZWt1vzk1TzSm0o3f8At+TaIfIcnxrbEXD5Ndck/8j8oGUOcJYf2u4FyelJpwwfVICZr1e/LCBs5I95Jwe4hNDE1XtNVhBcunOMrh+ezuCWdeZsx/0g07MG/PZyG/nrkuU8s= Received: by 10.66.250.17 with SMTP id x17mr4529ugh; Thu, 01 Jun 2006 11:05:51 -0700 (PDT) Received: by 10.67.20.12 with HTTP; Thu, 1 Jun 2006 11:05:51 -0700 (PDT) Message-ID: Date: Thu, 1 Jun 2006 13:05:51 -0500 From: "Aaron Bono" To: "Collin Peters" Subject: Re: Am I crazy or is this SQL not possible Cc: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5597_9727834.1149185151046" References: X-Google-Sender-Auth: 4b98690450b69462 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/18 X-Sequence-Number: 25254 ------=_Part_5597_9727834.1149185151046 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline select my_sub.max_date, broadcast_history.status from ( SELECT MAX(date_sent) max_date, broadcast_id FROM broadcast_history GROUP BY broadcast_id ) my_sub inner join broadcast_history on ( broadcast_history.broadcast_id = my_sub.broadcast_id and broadcast_history.date_sent = my_sub.max_date ); This should work if the combined broadcast_id, date_sent is unique. If not, you will need to decide what record to pick in case of a tie. On 6/1/06, Collin Peters wrote: > > I am having some serious mental block here. Here is the abstract > version of my problem. I have a table like this: > > unique_id (PK) broadcast_id date_sent status > 1 1 2005-04-04 30 > 2 1 2005-04-01 30 > 3 1 2005-05-20 10 > 4 2 2005-05-29 30 > > So it is a table that stores broadcasts including the broadcast_id, > the date sent, and the status of the broadcast. > > 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. > > SELECT MAX(date_sent), status > FROM broadcast_history > GROUP BY broadcast_id > > How do I get the status for the most recent date_sent using GROUP BY? > > DISTINCT also doesn't work > > SELECT DISTINCT ON (email_broadcast_id) * > FROM email_broadcast_history > ORDER BY date_sent > > As you have to have the DISTINCT fields matching the ORDER BY fields. > I have to ORDER BY date_sent, but I can't do a DISTINCT on date_sent > > I keep thinking am I missing something. Does anybody have any ideas? > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > -- ================================================================== Aaron Bono President Aranya Software Technologies, Inc. http://www.aranya.com We take care of your technology needs. Phone: (816) 695-6071 ================================================================== ------=_Part_5597_9727834.1149185151046 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline select my_sub.max_date, broadcast_history.status
from (
SELECT MAX(date_sent) max_date, broadcast_id
FROM broadcast_history
GROUP BY broadcast_id
) my_sub
inner join broadcast_history on (
broadcast_history.broadcast_id = my_sub.broadcast_id
and broadcast_history.date_sent = my_sub.max_date
);

This should work if the combined broadcast_id, date_sent is unique.  If not, you will need to decide what record to pick in case of a tie.

On 6/1/06, Collin Peters <cadiolis@gmail.com> wrote:
I am having some serious mental block here.  Here is the abstract
version of my problem.  I have a table like this:

unique_id (PK)   broadcast_id   date_sent      status
1                      1                     2005-04-04    30
2                      1                     2005-04-01     30
3                      1                     2005-05-20     10
4                      2                     2005-05-29     30

So it is a table that stores broadcasts including the broadcast_id,
the date sent, and the status of the broadcast.

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.

SELECT MAX(date_sent), status
FROM broadcast_history
GROUP BY broadcast_id

How do I get the status for the most recent date_sent using GROUP BY?

DISTINCT also doesn't work

SELECT DISTINCT ON (email_broadcast_id) *
FROM email_broadcast_history
ORDER BY date_sent

As you have to have the DISTINCT fields matching the ORDER BY fields.
I have to ORDER BY date_sent, but I can't do a DISTINCT on date_sent

I keep thinking am I missing something.  Does anybody have any ideas?

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster



--
==================================================================
Aaron Bono
President                            Aranya Software Technologies, Inc.
http://www.aranya.com         We take care of your technology needs.
Phone: (816) 695-6071
================================================================== ------=_Part_5597_9727834.1149185151046--