Message-ID: <28789564.1149192395462.JavaMail.root@eastrmwml05.mgt.cox.net>
Date: Thu, 1 Jun 2006 16:06:35 -0400
From: <mark.dingee@cox.net>
To: Collin Peters <cadiolis@gmail.com>
Subject: Re: Am I crazy or is this SQL not possible
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Sensitivity: Normal

Collin,

I have a similar circumstance in one of my own apps.  I operate under
the simple presumption that the unique_id is sequential and thus the
record with the highest unique_id is the most recent entry.  In that
case I use a query such as 

select * 
from broadcast_history
where unique_id in (
	select broadcast_id, max(unique_id) 
	from broadcast_history
	group by broadcast_id)

which permits me to examine the entire record which is necessary in my
situation.

Good luck
Mark

On Thu, 2006-06-01 at 10:43 -0700, 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