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
