<br />Sorry to stick my nose in here...<br />would not this work better?<br /><br />SELECT
broadcast_id,date_sent,statusfrom broadcast_history where<br />unique_id in (<br />SELECT max(unique_id) from
broadcast_historygroup by broadcast_id);<br /><br />Seems like a simpler option.<br /><br /><p> <font size="2">-----
OriginalMessage ------<br /> <b>From:</b>Patrick Jacquot<br /> <b>Sent:</b>Friday, June 02, 2006 10:45<br
/> <b>To:</b>CollinPeters 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></td></tr></tbody></table><font
size="2">CollinPeters wrote:<br /><br />> I am having some serious mental block here. Here is the abstract<br />>
versionof 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
statusof the broadcast.<br />><br />> What I would like to do is simply get the last date_sent and it's<br />>
statusfor 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'twork<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
missingsomething. 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
versionsbelow 8.0, the planner will ignore your desire to<br /> choose an index scan if your joining column's
datatypesdo not<br /> match<br /> <br /></font>
