From: Klay Martens <kmartens@wol.co.za>
To: patrick.jacquot@anpe.fr
To: cadiolis@gmail.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Am I crazy or is this SQL not possible
Date: Fri, 02 Jun 2006 13:53:42 +0200
Message-ID: <6c800ab64b48492fa88fa92f4fc28625@wol.co.za> (raw)
<BR>Sorry to stick my nose in here...<BR>would not this work better?<BR><BR>SELECT broadcast_id,date_sent,status from broadcast_history where<BR>unique_id in (<BR>SELECT max(unique_id) from broadcast_history group by broadcast_id);<BR><BR>Seems like a simpler option.<BR><BR>
<P> <FONT size=2>----- Original Message ------<BR> <B>From:</B>Patrick Jacquot<BR> <B>Sent:</B>Friday, June 02, 2006 10:45<BR> <B>To:</B>Collin Peters cadiolis@gmail.com; <BR> <B>Cc: </B>pgsql-sql@postgresql.org; <BR> <B>Subject:</B>Re: [SQL] Am I crazy or is this SQL not possible</FONT>
<P>
<TABLE>
<TBODY>
<TR>
<TD>
<META content="text/html; charset=windows-874" http-eqviv="Content-Type"><FONT size=2>Collin Peters wrote:<BR><BR>> I am having some serious mental block here. Here is the abstract<BR>> version of my problem. I have a table like this:<BR>><BR>> unique_id (PK) broadcast_id date_sent status<BR>> 1 1 2005-04-04 30<BR>> 2 1 2005-04-01 30<BR>> 3 1 2005-05-20 10<BR>> 4 2 2005-05-29 30<BR>><BR>> So it is a table that stores broadcasts including the broadcast_id,<BR>> the date sent, and the status of the broadcast.<BR>><BR>> What I would like to do is simply get the last date_sent and it's<BR>> status for every broadcast. I can't do a GROUP BY because I can't put<BR>> an aggregate on the status column.<BR>><BR>> SELECT MAX(date_sent), status<BR>> FROM broadcast_history<BR>> GROUP BY broadcast_id<BR>><BR>> How do I get the status for the most recent date_sent using GROUP BY?<BR>><BR>> DISTINCT also doesn't work<BR>><BR>> SELECT DISTINCT ON (email_broadcast_id) *<BR>> FROM email_broadcast_history<BR>> ORDER BY date_sent<BR>><BR>> As you have to have the DISTINCT fields matching the ORDER BY fields.<BR>> I have to ORDER BY date_sent, but I can't do a DISTINCT on date_sent<BR>><BR>> I keep thinking am I missing something. Does anybody have any ideas?<BR>><BR>> ---------------------------(end of broadcast)---------------------------<BR>> TIP 2: Don't 'kill -9' the postmaster<BR>><BR>You can also try<BR>SELECT * from broadcast_history A WHERE NOT EXISTS<BR>(SELECT * from broadcast_history B WHERE B.date_sent >A.date_sent)<BR>There isn't any PostgreSQL-ism, just a correlated subrequest wich is <BR>perfectly standars, afaik<BR><BR><BR> ---------------------------(end of broadcast)---------------------------<BR> TIP 9: In versions below 8.0, the planner will ignore your desire to<BR> choose an index scan if your joining column's datatypes do not<BR> match<BR> <BR></FONT></TD></TR></TBODY></TABLE></P>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kmartens@wol.co.za, patrick.jacquot@anpe.fr, cadiolis@gmail.com
Subject: Re: Am I crazy or is this SQL not possible
In-Reply-To: <6c800ab64b48492fa88fa92f4fc28625@wol.co.za>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox