Re: Help with SQL staterment

-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org [mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Thom Brown
Sent: Thursday, June 30, 2011 3:16 PM
To: Chris Campbell
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Help with SQL staterment

On 30 June 2011 22:59, Chris Campbell <ccampbell(at)cascadeds(dot)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 based on the max(paymentdate). When I add paymentkey to the mix I keep ending up with a cartesianed product showing multiple paymentkeys.

> 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 mentioned it