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