Re: agregates

Here si the explain plan on this. Not very fast.

Seq Scan on tblpayment p1 (cost=0.00..5422453.14 rows=1 width=8)
SubPlan
-> Limit (cost=36.69..36.69 rows=1 width=4)
-> Sort (cost=36.69..36.69 rows=2 width=4)
-> Index Scan using tblpayment_idx on tblpayment p2
(cost=0.00..36.68 rows=2 width=4)

EXPLAIN

Good news though I figured out how to get it done quickly.

select
lastmonth.paymentid as lmpayment ,max(beforemonth.paymentid) as
paymentid, date_trunc('month',lastmonth.paymentdate) as
lmpaymentdate,lastmonth.loginid
from tblpayment as lastmonth, tblpayment as
beforemonth
where
lastmonth.customerid =
beforemonth.customerid
and lastmonth.paymentid >
beforemonth.paymentid
and lastmonth.paymentdebit > 0
and beforemonth.paymentdebit > 0
group by
lastmonth.paymentid,
date_trunc('month',lastmonth.paymentdate), lastmonth.customerid
) as tp1 join tblpayment using
(paymentid)

I wanted the payment before the last on a per month basis so I added
that in. If you take out the references to date then if would give the
payment before the last payment for each customer
Here is the explain for this query

Aggregate (cost=17355.43..17560.13 rows=2047 width=24)
-> Group (cost=17355.43..17508.95 rows=20470 width=24)
-> Sort (cost=17355.43..17355.43 rows=20470 width=24)
-> Merge Join (cost=14836.39..15889.66 rows=20470
width=24)
-> Sort (cost=7418.19..7418.19 rows=26424
width=16)
-> Seq Scan on tblpayment lastmonth
(cost=0.00..5477.39 rows=26424 width=16)
-> Sort (cost=7418.19..7418.19 rows=26424 width=8)
-> Seq Scan on tblpayment beforemonth
(cost=0.00..5477.39 rows=26424 width=8)

EXPLAIN

A whole lot better.

Thanks for the suggestions though.

David Blood
Boise, ID

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Jean-Luc
Lachance
Sent: Thursday, January 23, 2003 1:56 PM
To: Andrew Sullivan; David Blood
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] agregates

Sure it does!

select customersid, paymentid
from tblpayment p1
where paymentdebit > 0 and paymentid = (
select paymentid
from tblpayment p2
where p2.paymentdebit > 0 and p2.customerid = p1.customerid
order by paymentid desc limit 1 offset 1);

JLL

Andrew Sullivan wrote:
>
> On Wed, Jan 22, 2003 at 05:15:44PM -0500, Eric B. Ridge wrote:
> >
> > wouldn't you want LIMIT 2 OFFSET 1 ?
>
> No, he only wants one record. But actually, he wanted one record per
> customer, so my suggestion didn't help.
>
> A
> --
> ----
> Andrew Sullivan 204-4141 Yonge Street
> Liberty RMS Toronto, Ontario Canada
> <andrew(at)libertyrms(dot)info> M2P 2A8
> +1 416 646 3304 x110
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo(at)postgresql(dot)org

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org