-----Original Message-----
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Thom Brown
Sent: Thursday, June 30, 2011 3:16 PM
To: Chris Campbell
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Help with SQL staterment
On 30 June 2011 22:59, Chris Campbell <ccampbell@cascadeds.com> wrote:
>> 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
>It should look something like this:
>SELECT contactkey, max(paymentdate)
>FROM my_table
>GROUP BY contactkey;
Yeah, that's what I started with. The problem is that I "need" the payment key returned in addition to the other fields
basedon the max(paymentdate). When I add paymentkey to the mix I keep ending up with a cartesianed product showing
multiplepaymentkeys.
> You'll need to adapt it for your joined query as I don't know what your join looks like.
The whole sub query thing is irrelevant to the result set I'm initially after. I probably shouldn't have even
mentionedit
