Am I crazy or is this SQL not possible

From: "Collin Peters" <cadiolis(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Am I crazy or is this SQL not possible
Date: 2006-06-01 17:43:14
Message-ID: df01c91b0606011043g61328d6habaee8fa037a5ccf@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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?

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message codeWarrior 2006-06-01 17:45:49 Re: Table design question
Previous Message postgres 2006-06-01 16:44:50 Re: Table design question