Message-ID: <447FFAAF.9050401@anpe.fr>
Date: Fri, 02 Jun 2006 10:45:35 +0200
From: Patrick Jacquot <patrick.jacquot@anpe.fr>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
MIME-Version: 1.0
To: Collin Peters <cadiolis@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Am I crazy or is this SQL not possible
References: <df01c91b0606011043g61328d6habaee8fa037a5ccf@mail.gmail.com>
In-Reply-To: <df01c91b0606011043g61328d6habaee8fa037a5ccf@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>
You can also try
SELECT * from broadcast_history A WHERE NOT EXISTS
 (SELECT * from broadcast_history B WHERE B.date_sent >A.date_sent)
There isn't any PostgreSQL-ism, just a correlated subrequest wich is 
perfectly standars, afaik