Re: agregates

From: "codeWarrior" <GPatnude(at)adelphia(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: agregates
Date: 2003-01-22 18:55:38
Message-ID: b0mp22$14fu$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

SORRY -- I realized you need to specify an order to get the next to last
record .. -- Check the PG docs on LIMIT (Section 7.2 under "SELECT")...

SELECT paymentid, customerid FROM tblpayment where paymentdebit > 0 ORDER
BY paymentid DESC LIMIT 1 OFFSET 1

"codeWarrior" <GPatnude(at)adelphia(dot)net> wrote in message
news:b0mo75$11ne$1(at)news(dot)hub(dot)org(dot)(dot)(dot)
> SELECT paymentid, customerid FROM tblpayment where paymentdebit > 0 LIMIT
1
> OFFSET 1
>
>
>
> ""David Blood"" <david(at)matraex(dot)com> wrote in message
> news:016b01c2c238$b40ca580$1f00a8c0(at)redwood(dot)(dot)(dot)
> > This is a multi-part message in MIME format.
> >
> > ------=_NextPart_000_016C_01C2C1FE.07ADCD80
> > Content-Type: text/plain;
> > charset="US-ASCII"
> > Content-Transfer-Encoding: 7bit
> >
> >
> > I would like to be a ble to get a bunch of payments from a payment
> > table. I want to return not the last payment the payment before that
> > for all customers.
> >
> > I have tried
> >
> > select max(paymentid) as paymentid
> > from tblpayment
> > where paymentdebit > 0
> > and paymentid not in (select max(paymentid) as paymentid
> > from tblpayment
> > where paymentdebit > 0
> > group by customerid)
> >
> >
> > the cost on this thru the roof
> >
> >
> > NOTICE: QUERY PLAN:
> >
> > Aggregate (cost=1520829785.44..1520829785.44 rows=1 width=4)
> > -> Seq Scan on tblpayment (cost=0.00..1520829707.94 rows=31002
> > width=4)
> > SubPlan
> > -> Materialize (cost=10336.74..10336.74 rows=6200 width=8)
> > -> Aggregate (cost=10026.72..10336.74 rows=6200
> > width=8)
> > -> Group (cost=10026.72..10181.73 rows=62005
> > width=8)
> > -> Sort (cost=10026.72..10026.72
> > rows=62005 width=8)
> > -> Seq Scan on tblpayment
> > (cost=0.00..5091.10 rows=62005 width=8)
> >
> >
> > the same query rewritten using exist has a better time but still much to
> > long
> >
> >
> > NOTICE: QUERY PLAN:
> >
> > Aggregate (cost=5769119.39..5769274.41 rows=3100 width=8)
> > -> Group (cost=5769119.39..5769196.90 rows=31002 width=8)
> > -> Sort (cost=5769119.39..5769119.39 rows=31002 width=8)
> > -> Seq Scan on tblpayment thismonth
> > (cost=0.00..5766806.60 rows=31002 width=8)
> > SubPlan
> > -> Limit (cost=39.16..39.16 rows=1 width=12)
> > -> Sort (cost=39.16..39.16 rows=5
> > width=12)
> > -> Index Scan using tblpayment_idx on
> > tblpayment (cost=0.00..39.11 rows=5 width=12)
> >
> > EXPLAIN
> >
> >
> > is there a better way to get the max - 1?
> > I could loop through and run the queriy for each customer but the cost
> > on ten thousand quesries is rather high also?
> >
> > David Blood
> > Boise, ID
> >
> > David Blood
> > Boise, ID
> >
> >
> > ------=_NextPart_000_016C_01C2C1FE.07ADCD80
> > Content-Type: text/html;
> > charset="US-ASCII"
> > Content-Transfer-Encoding: quoted-printable
> >
> > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> > <HTML><HEAD>
> > <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html;
> charset=3Dus-ascii">
> > <TITLE>Message</TITLE>
> >
> > <META content=3D"MSHTML 6.00.2722.900" name=3DGENERATOR></HEAD>
> > <BODY>
> > <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
> > <DIV>
> > <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial size=3D2>I
would
> l=
> > ike to be a=20
> > ble to get a bunch of payments from a payment table.&nbsp; I want to
> return=
> > not=20
> > the last payment the payment before that for all
> customers.</FONT></SPAN></=
> > DIV>
> > <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial=20
> > size=3D2></FONT></SPAN>&nbsp;</DIV>
> > <DIV><SPAN class=3D096202915-22012003>I have tried</SPAN></DIV><SPAN=20
> > class=3D096202915-22012003>
> > <DIV><BR><FONT face=3DArial size=3D2>select max(paymentid) as=20
> > paymentid<BR>&nbsp;&nbsp;&nbsp;from tblpayment
> <BR>&nbsp;&nbsp;&nbsp;where=
> > =20
> > paymentdebit &gt; 0<BR>&nbsp;&nbsp;&nbsp;and paymentid not in (select=20
> > max(paymentid) as paymentid<BR>&nbsp;&nbsp;&nbsp;from tblpayment=20
> > <BR>&nbsp;&nbsp;&nbsp;where paymentdebit &gt;
0<BR>&nbsp;&nbsp;&nbsp;group
> =
> > by=20
> > customerid)</FONT></DIV>
> > <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
> > <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
> > <DIV></SPAN><SPAN class=3D096202915-22012003><FONT face=3DArial
> size=3D2>th=
> > e cost on=20
> > this thru the roof</FONT></SPAN></DIV>
> > <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial=20
> > size=3D2></FONT></SPAN>&nbsp;</DIV><SPAN class=3D096202915-22012003>
> > <DIV><BR><FONT face=3DArial size=3D2>NOTICE:&nbsp; QUERY
> PLAN:</FONT></DIV>
> > <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
> > <DIV><FONT face=3DArial size=3D2>Aggregate&nbsp;
> (cost=3D1520829785.44..152=
> > 0829785.44=20
> > rows=3D1 width=3D4)<BR>&nbsp; -&gt;&nbsp; Seq Scan on
tblpayment&nbsp;=20
> > (cost=3D0.00..1520829707.94 rows=3D31002=20
> > width=3D4)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
> >
>
SubPlan<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbs=
> > p;=20
> > Materialize&nbsp; (cost=3D10336.74..10336.74 rows=3D6200=20
> >
>
width=3D8)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
> > nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
> > -&gt;&nbsp; Aggregate&nbsp; (cost=3D10026.72..10336.74 rows=3D6200=20
> >
>
width=3D8)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
> > nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
> > -&gt;&nbsp; Group&nbsp; (cost=3D10026.72..10181.73 rows=3D62005=20
> >
>
width=3D8)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
> >
>
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
> > p;&nbsp;&nbsp;&nbsp;&nbsp;=20
> > -&gt;&nbsp; Sort&nbsp; (cost=3D10026.72..10026.72 rows=3D62005=20
> >
>
width=3D8)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
> >
>
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
> > p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
> > -&gt;&nbsp; Seq Scan on tblpayment&nbsp; (cost=3D0.00..5091.10
> rows=3D62005=
> > =20
> > width=3D8)<BR></FONT></DIV>
> > <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
> > <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial size=3D2>the
same
> =
> > query=20
> > rewritten using exist has a better time but still much to=20
> > long</FONT></SPAN></DIV>
> > <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial=20
> > size=3D2></FONT></SPAN>&nbsp;</DIV><SPAN class=3D096202915-22012003>
> > <DIV><BR><FONT face=3DArial size=3D2>NOTICE:&nbsp; QUERY
> PLAN:</FONT></DIV>
> > <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
> > <DIV><FONT face=3DArial size=3D2>Aggregate&nbsp;
> (cost=3D5769119.39..576927=
> > 4.41=20
> > rows=3D3100 width=3D8)<BR>&nbsp; -&gt;&nbsp; Group&nbsp;=20
> > (cost=3D5769119.39..5769196.90 rows=3D31002=20
> > width=3D8)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp;
> Sort&n=
> > bsp;=20
> > (cost=3D5769119.39..5769119.39 rows=3D31002=20
> >
>
width=3D8)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
> > nbsp;&nbsp;&nbsp;=20
> > -&gt;&nbsp; Seq Scan on tblpayment thismonth&nbsp;
> (cost=3D0.00..5766806.60=
> > =20
> > rows=3D31002=20
> >
>
width=3D8)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
> > nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
> >
>
SubPlan<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
> > p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
> > -&gt;&nbsp; Limit&nbsp; (cost=3D39.16..39.16 rows=3D1=20
> >
>
width=3D12)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
> >
>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
> > sp;&nbsp;&nbsp;&nbsp;&nbsp;=20
> > -&gt;&nbsp; Sort&nbsp; (cost=3D39.16..39.16 rows=3D5=20
> >
>
width=3D12)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
> >
>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
> > sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
> > -&gt;&nbsp; Index Scan using tblpayment_idx on tblpayment&nbsp;=20
> > (cost=3D0.00..39.11 rows=3D5 width=3D12)</FONT></DIV>
> > <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
> > <DIV><FONT face=3DArial size=3D2>EXPLAIN<BR></FONT></DIV>
> > <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
> > <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial size=3D2>is
there
> =
> > a better=20
> > way to get the max - 1?</FONT></SPAN></DIV>
> > <DIV><SPAN class=3D096202915-22012003><FONT face=3DArial size=3D2>I
could
> l=
> > oop through=20
> > and run the queriy for each customer but the cost on ten thousand
quesries
> =
> > is=20
> > rather high also?</FONT></SPAN></DIV></SPAN></SPAN>
> > <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
> > <DIV align=3Dleft><FONT face=3DArial size=3D2>David Blood</FONT></DIV>
> > <DIV align=3Dleft><FONT face=3DArial size=3D2>Boise, ID</FONT></DIV>
> > <DIV>&nbsp;</DIV></DIV>
> > <DIV align=3Dleft><FONT face=3DArial size=3D2>David Blood</FONT></DIV>
> > <DIV align=3Dleft><FONT face=3DArial size=3D2>Boise, ID</FONT></DIV>
> > <DIV>&nbsp;</DIV></BODY></HTML>
> >
> > ------=_NextPart_000_016C_01C2C1FE.07ADCD80--
> >
> >
>
>

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Ed L. 2003-01-22 19:10:17 7.2.1 clients + 7.2.3 server?
Previous Message codeWarrior 2003-01-22 18:41:11 Re: agregates