Re: Help with SQL staterment

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