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 B16DF9FA5BF for ; Fri, 2 Jun 2006 09:12:53 -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 51189-10 for ; Fri, 2 Jun 2006 09:12:44 -0300 (ADT) X-Greylist: delayed 00:18:57.278164 by SQLgrey- Received: from coral.mweb.co.za (coral.mweb.co.za [196.2.50.72]) by postgresql.org (Postfix) with ESMTP id 442DC9FA040 for ; Fri, 2 Jun 2006 09:12:44 -0300 (ADT) Received: from coral.mweb.co.za (localhost.localdomain [127.0.0.1]) by pwfilter01.mweb.co.za (Postfix) with ESMTP id 5E0FA50512; Fri, 2 Jun 2006 13:53:43 +0200 (SAST) Received: from MWS41 (unknown [196.2.63.105]) by coral.mweb.co.za (Postfix) with ESMTP id 5316B5031A; Fri, 2 Jun 2006 13:53:43 +0200 (SAST) From: "Klay Martens" To: patrick.jacquot@anpe.fr, cadiolis@gmail.com CC: pgsql-sql@postgresql.org Message-ID: <6c800ab64b48492fa88fa92f4fc28625@wol.co.za> Date: Fri, 02 Jun 2006 13:53:42 +0200 X-Priority: 3 Subject: Re: Am I crazy or is this SQL not possible MIME-Version: 1.0 Content-type: text/html; charset=Windows-1252 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: ClamAV using ClamSMTP X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/38 X-Sequence-Number: 25274
Sorry to stick my nose in here=2E=2E=2E
would not this work bett= er?

SELECT broadcast_id,date_sent,status from broadcast_history = where
unique_id in (
SELECT max(unique_id) from broadcast_history= group by broadcast_id);

Seems like a simpler option=2E

----- Original Message ------
From:= Patrick Jacquot
Sent:Friday, June 02, 2006 10:45 To:Collin Peters cadiolis@gmail=2Ecom;
Cc: <= /B>pgsql-sql@postgresql=2Eorg;
Subject:Re: [SQL] Am I = crazy or is this SQL not possible

=

Collin Peters wrote:

> I am having som= e serious mental block here=2E Here is the abstract
> version of = my problem=2E I have a table like this:
>
> unique_id (PK) = broadcast_id date_sent status
> 1 1 2005-04-04 30
> 2 1 200= 5-04-01 30
> 3 1 2005-05-20 10
> 4 2 2005-05-29 30
><= BR>> So it is a table that stores broadcasts including the broadcast= _id,
> the date sent, and the status of the broadcast=2E
><= BR>> What I would like to do is simply get the last date_sent and it= 's
> status for every broadcast=2E I can't do a GROUP BY because = I can't put
> an aggregate on the status column=2E
>
>= ; SELECT MAX(date_sent), status
> FROM broadcast_history
> = GROUP BY broadcast_id
>
> How do I get the status for the m= ost recent date_sent using GROUP BY?
>
> DISTINCT also does= n't work
>
> SELECT DISTINCT ON (email_broadcast_id) *
&= gt; FROM email_broadcast_history
> ORDER BY date_sent
>
= > As you have to have the DISTINCT fields matching the ORDER BY fiel= ds=2E
> I have to ORDER BY date_sent, but I can't do a DISTINCT o= n date_sent
>
> I keep thinking am I missing something=2E D= oes anybody have any ideas?
>
> ---------------------------= (end of broadcast)---------------------------
> TIP 2: Don't 'kil= l -9' the postmaster
>
You can also try
SELECT * from broad= cast_history A WHERE NOT EXISTS
(SELECT * from broadcast_history B W= HERE B=2Edate_sent >A=2Edate_sent)
There isn't any PostgreSQL-ism= , just a correlated subrequest wich is
perfectly standars, afaik

   ---------------------------(end of broadcast= )---------------------------
   TIP 9: In versions be= low 8=2E0, the planner will ignore your desire to
   = choose an index scan if your joining column's datatypes do not
&nbs= p;   match