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
May not be the most efficient but seems to work here.
Select broadcast_id,status from broadcast_history bh1 where
bh1.date_sent = (select max(date_sent) from broadcast_history bh2 where
bh1.broadcast_id=bh2.broadcast_id) order by bh1.broadcast_id;
Oisin
