From: Aaron Bono <postgresql@aranya.com>
To: Collin Peters <cadiolis@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Am I crazy or is this SQL not possible
Date: Thu, 1 Jun 2006 13:05:51 -0500
Message-ID: <bf05e51c0606011105i417f3d50vd6bff79b1fa17aaa@mail.gmail.com> (raw)
In-Reply-To: <df01c91b0606011043g61328d6habaee8fa037a5ccf@mail.gmail.com>
References: <df01c91b0606011043g61328d6habaee8fa037a5ccf@mail.gmail.com>
select my_sub.max_date, broadcast_history.status
from (
SELECT MAX(date_sent) max_date, broadcast_id
FROM broadcast_history
GROUP BY broadcast_id
) my_sub
inner join broadcast_history on (
broadcast_history.broadcast_id = my_sub.broadcast_id
and broadcast_history.date_sent = my_sub.max_date
);
This should work if the combined broadcast_id, date_sent is unique. If not,
you will need to decide what record to pick in case of a tie.
On 6/1/06, Collin Peters <cadiolis@gmail.com> 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
>
--
==================================================================
Aaron Bono
President Aranya Software Technologies, Inc.
http://www.aranya.com We take care of your technology needs.
Phone: (816) 695-6071
==================================================================
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: postgresql@aranya.com, cadiolis@gmail.com
Subject: Re: Am I crazy or is this SQL not possible
In-Reply-To: <bf05e51c0606011105i417f3d50vd6bff79b1fa17aaa@mail.gmail.com>
* 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