Re: Help with SQL staterment

On 30 June 2011 23:25, Chris Campbell <ccampbell@cascadeds.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
fieldsbased on the max(paymentdate).  When I add paymentkey to the mix I keep ending up with a cartesianed product
showingmultiple 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