Skip site navigation (1) Skip section navigation (2)

Re: Help with SQL staterment

From: Thom Brown <thom(at)linux(dot)com>
To: Chris Campbell <ccampbell(at)cascadeds(dot)com>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Help with SQL staterment
Date: 2011-06-30 23:04:46
Message-ID: BANLkTin2xvC1PZebXO3XD8YNXzPtmYoE3w@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On 30 June 2011 23:25, Chris Campbell <ccampbell(at)cascadeds(dot)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 fields based on the max(paymentdate).  When I add paymentkey to the mix I keep ending up with a cartesianed product showing multiple 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

In response to

Responses

pgsql-novice by date

Next:From: Steve CrawfordDate: 2011-06-30 23:23:41
Subject: Re: Help with SQL staterment
Previous:From: Steve CrawfordDate: 2011-06-30 22:49:52
Subject: Re: Help with SQL staterment

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group