From: | Thom Brown <thom(at)linux(dot)com> |
---|---|
To: | Steve Crawford <scrawford(at)pinpointresearch(dot)com> |
Cc: | Chris Campbell <ccampbell(at)cascadeds(dot)com>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Help with SQL staterment |
Date: | 2011-06-30 23:28:31 |
Message-ID: | BANLkTikWtpu3UnSKQRrXqFAuWMFe9s9EQQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On 1 July 2011 00:23, Steve Crawford <scrawford(at)pinpointresearch(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-07-01 00:12:16 | Re: change data type 'money' to '€' |
Previous Message | Steve Crawford | 2011-06-30 23:23:41 | Re: Help with SQL staterment |