Thread: Help with SQL staterment

On 30 June 2011 22:59, Chris Campbell <ccampbell@cascadeds.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

-----Original Message-----
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Thom Brown
Sent: Thursday, June 30, 2011 3:16 PM
To: Chris Campbell
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Help with SQL staterment

On 30 June 2011 22:59, Chris Campbell <ccampbell@cascadeds.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;

Yeah, that's what I started with. The problem is that I "need" the payment key returned in addition to the other fields
basedon the max(paymentdate).  When I add paymentkey to the mix I keep ending up with a cartesianed product showing
multiplepaymentkeys.   

> You'll need to adapt it for your joined query as I don't know what your join looks like.
The whole sub query thing is irrelevant to the result set I'm initially after.  I probably shouldn't have even
mentionedit 

On 06/30/2011 02:59 PM, Chris Campbell wrote:

Hello list,

 

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

 

Any assistance would be greatly appreciated.

 

Thank you,

 

Chris

I am going to make some assumptions here:

1. PaymentKey is unique.
2. There can never be more than one payment for a given ContactKey on any given date (and this should be enforced by table-constraints).

If that is not true, the whole thing blows up.

One way (untested and probably slow on big tables) is with a sub-select:
select * from yourtable o where PaymentDate = (select max(PaymentDate) from yourtable i where i.ContactKey = o.ContactKey);

Probably a better way is with common table expressions:
with foo as (select PaymentKey, max(PaymentDate) as MaxPayDate from yourtable group by 1)
select * from yourtable y where exists (select 1 from foo f where (f.PaymentKey,f.MaxPayDate) = (y.PaymentKey,y.PaymentDate);

Cheers,
Steve

On 30 June 2011 23:25, Chris Campbell <ccampbell@cascadeds.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
fieldsbased on the max(paymentdate).  When I add paymentkey to the mix I keep ending up with a cartesianed product
showingmultiple 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

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.

Cheers,
Steve

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

> 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.

I would have never thought that "SELECT DISTINCT ON" would return that result set, but amazingly it works like a charm.
Obviously I need to study up on that syntax. 

Many thanks for all the assistance.

Chris

Working with Jasper reports and the database fields have all disappeared
from the program.  The paths are right but the fields are gone.  Any help?
The developer of the app that uses PG as it's back end has not been very
helpful...

On Fri, Jul 1, 2011 at 5:55 AM, Phillip B Childs <it@marisusa.com> wrote:
> Any help?

The first bit of help that can be offered would be to read the
following link.  It help you to help persons on the mailing list to
help you better.

http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

> The developer of the app that uses PG as it's back end has not been very
> helpful...

I'm sorry to hear that your developer isn't very helpful.


--
Regards,
Richard Broersma Jr.