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:content-transfer-encoding:content-disposition;
	b=sVJ3I7DBiuXdiWEugowbxgiBbZun+m17IFAdme+Rj6b5kjX0AyMXYt9RBmOVnPeftVvqzhtt8JtlGSc79QOUEJJFk0B5fRWUHVtg5hbTGoKrpODCPZFPenFUN7YVbColB4AIKE2YQ0tDnFr1yoosUIiQYx3o/ZP+7ZKtozS0HqM=
Message-ID: <df01c91b0606011043g61328d6habaee8fa037a5ccf@mail.gmail.com>
Date: Thu, 1 Jun 2006 10:43:14 -0700
From: "Collin Peters" <cadiolis@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Am I crazy or is this SQL not possible
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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?