Re: Help with SQL staterment

On 30 June 2011 23:25, Chris Campbell <ccampbell(at)cascadeds(dot)com> wrote:
>>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 can try this:

SELECT DISTINCT ON (contactkey)
paymentkey, contactkey, paymentdate
FROM
my_table
ORDER BY
contactkey, paymentdate DESC

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company