----- Original Message ------
 From:Patrick Jacquot
 Sent:Friday, June 02, 2006 10:45
 To:Collin Peters cadiolis@gmail.com;
 Cc: pgsql-sql@postgresql.org;
 Subject:Re: [SQL] Am I crazy or is this SQL not possible

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    ---------------------------(end of broadcast)---------------------------    TIP 9: In versions below 8.0, the planner will ignore your desire to     choose an index scan if your joining column's datatypes do not     match