On 1 July 2011 00:23, Steve Crawford <scrawford@pinpointresearch.com> wrote:
> On 06/30/2011 04:04 PM, Thom Brown wrote:
>>
>> SELECT DISTINCT ON (contactkey)
>> paymentkey, contactkey, paymentdate
>> FROM
>> my_table
>> ORDER BY
>> contactkey, paymentdate DESC
>
> Gives the same result as my example but your solution is much cleaner.
Yes, I initially started thinking of some convoluted solution like:
WITH output_table AS
(
SELECT
first_value(paymentkey) OVER w paymentkey,
contactkey,
first_value(paymentdate) OVER w paymentdate
FROM
my_table
GROUP BY
paymentkey, contactkey
WINDOW w AS (PARTITION BY contactkey ORDER BY paymentdate DESC)
)
SELECT paymentkey, contactkey, paymentdate
FROM output_table
GROUP BY paymentkey, contactkey, paymentdate;
It will work, but it's massively over-complicated for this scenario. :)
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company