From: | Steve Crawford <scrawford(at)pinpointresearch(dot)com> |
---|---|
To: | Chris Campbell <ccampbell(at)cascadeds(dot)com> |
Cc: | "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Help with SQL staterment |
Date: | 2011-06-30 22:49:52 |
Message-ID: | 4E0CFD90.5090201@pinpointresearch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On 06/30/2011 02:59 PM, Chris Campbell wrote:
>
> Hello list,
>
> Using PostgreSQL 9.0.2 I'm trying to write a sql statement to extract
> specific rows from the following data set. I've looked at the
> documentation that talks about the Max() function but the example
> doesn't go far enough:
>
> The sample fields/dataset are as follows
>
> PaymentKey, ContactKey, PaymentDate
>
> 1, 100, 01/01/2011
>
> 2, 100, 12/30/2010
>
> 3, 100, 12/31/2010
>
> 4, 101, 01/02/2011
>
> 5, 101, 12/25/2010
>
> What I want returned are rows grouped by contactkey showing the
> corresponding payment key and payment date for the record that has the
> max (newest) payment date. So I would want my result set to look like
> this:
>
> 1, 100, 01/01/2011
>
> 4, 101, 01/02/2011
>
> I would be using this query as a sub query that is (left) joined to a
> master query by ContactKey
>
> Any assistance would be greatly appreciated.
>
> Thank you,
>
> Chris
>
I am going to make some assumptions here:
1. PaymentKey is unique.
2. There can never be more than one payment for a given ContactKey on
any given date (and this should be enforced by table-constraints).
If that is not true, the whole thing blows up.
One way (untested and probably slow on big tables) is with a sub-select:
select * from yourtable o where PaymentDate = (select max(PaymentDate)
from yourtable i where i.ContactKey = o.ContactKey);
Probably a better way is with common table expressions:
with foo as (select PaymentKey, max(PaymentDate) as MaxPayDate from
yourtable group by 1)
select * from yourtable y where exists (select 1 from foo f where
(f.PaymentKey,f.MaxPayDate) = (y.PaymentKey,y.PaymentDate);
Cheers,
Steve
From | Date | Subject | |
---|---|---|---|
Next Message | Thom Brown | 2011-06-30 23:04:46 | Re: Help with SQL staterment |
Previous Message | Chris Campbell | 2011-06-30 22:25:44 | Re: Help with SQL staterment |