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 22:15:32 |
Message-ID: | BANLkTimFNg0SeWkGgqMe_=CeqaLpUc1ehg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On 30 June 2011 22:59, Chris Campbell <ccampbell(at)cascadeds(dot)com> wrote:
> Using PostgreSQL 9.0.2 I’m trying to write a sql statement to extract
> specific rows from the following data set. I’ve looked at the documentation
> that talks about the Max() function but the example doesn’t go far enough:
>
> The sample fields/dataset are as follows
>
> PaymentKey, ContactKey, PaymentDate
> 1, 100, 01/01/2011
> 2, 100, 12/30/2010
> 3, 100, 12/31/2010
> 4, 101, 01/02/2011
> 5, 101, 12/25/2010
>
> What I want returned are rows grouped by contactkey showing the
> corresponding payment key and payment date for the record that has the max
> (newest) payment date. So I would want my result set to look like this:
>
> 1, 100, 01/01/2011
> 4, 101, 01/02/2011
>
> I would be using this query as a sub query that is (left) joined to a master
> query by ContactKey
It should look something like this:
SELECT contactkey, max(paymentdate)
FROM my_table
GROUP BY contactkey;
You'll need to adapt it for your joined query as I don't know what
your join looks like.
Regards
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 | Chris Campbell | 2011-06-30 22:25:44 | Re: Help with SQL staterment |
Previous Message | Chris Campbell | 2011-06-30 21:59:57 | Help with SQL staterment |