| 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> </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. 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> </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> from tblpayment
> <BR> where=
> > =20
> > paymentdebit > 0<BR> and paymentid not in (select=20
> > max(paymentid) as paymentid<BR> from tblpayment=20
> > <BR> where paymentdebit >
0<BR> group
> =
> > by=20
> > customerid)</FONT></DIV>
> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> > <DIV><FONT face=3DArial size=3D2></FONT> </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> </DIV><SPAN class=3D096202915-22012003>
> > <DIV><BR><FONT face=3DArial size=3D2>NOTICE: QUERY
> PLAN:</FONT></DIV>
> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> > <DIV><FONT face=3DArial size=3D2>Aggregate
> (cost=3D1520829785.44..152=
> > 0829785.44=20
> > rows=3D1 width=3D4)<BR> -> Seq Scan on
tblpayment =20
> > (cost=3D0.00..1520829707.94 rows=3D31002=20
> > width=3D4)<BR> =20
> >
>
SubPlan<BR> ->&nbs=
> > p;=20
> > Materialize (cost=3D10336.74..10336.74 rows=3D6200=20
> >
>
width=3D8)<BR> &=
> > nbsp; =20
> > -> Aggregate (cost=3D10026.72..10336.74 rows=3D6200=20
> >
>
width=3D8)<BR> &=
> > nbsp; =20
> > -> Group (cost=3D10026.72..10181.73 rows=3D62005=20
> >
>
width=3D8)<BR> &=
> >
>
nbsp; &nbs=
> > p; =20
> > -> Sort (cost=3D10026.72..10026.72 rows=3D62005=20
> >
>
width=3D8)<BR> &=
> >
>
nbsp; &nbs=
> > p; =20
> > -> Seq Scan on tblpayment (cost=3D0.00..5091.10
> rows=3D62005=
> > =20
> > width=3D8)<BR></FONT></DIV>
> > <DIV><FONT face=3DArial size=3D2></FONT> </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> </DIV><SPAN class=3D096202915-22012003>
> > <DIV><BR><FONT face=3DArial size=3D2>NOTICE: QUERY
> PLAN:</FONT></DIV>
> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> > <DIV><FONT face=3DArial size=3D2>Aggregate
> (cost=3D5769119.39..576927=
> > 4.41=20
> > rows=3D3100 width=3D8)<BR> -> Group =20
> > (cost=3D5769119.39..5769196.90 rows=3D31002=20
> > width=3D8)<BR> ->
> Sort&n=
> > bsp;=20
> > (cost=3D5769119.39..5769119.39 rows=3D31002=20
> >
>
width=3D8)<BR> &=
> > nbsp; =20
> > -> Seq Scan on tblpayment thismonth
> (cost=3D0.00..5766806.60=
> > =20
> > rows=3D31002=20
> >
>
width=3D8)<BR> &=
> > nbsp; =20
> >
>
SubPlan<BR> &nbs=
> > p; =20
> > -> Limit (cost=3D39.16..39.16 rows=3D1=20
> >
>
width=3D12)<BR> =
> >
>
&nb=
> > sp; =20
> > -> Sort (cost=3D39.16..39.16 rows=3D5=20
> >
>
width=3D12)<BR> =
> >
>
&nb=
> > sp; =20
> > -> Index Scan using tblpayment_idx on tblpayment =20
> > (cost=3D0.00..39.11 rows=3D5 width=3D12)</FONT></DIV>
> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> > <DIV><FONT face=3DArial size=3D2>EXPLAIN<BR></FONT></DIV>
> > <DIV><FONT face=3DArial size=3D2></FONT> </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> </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> </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> </DIV></BODY></HTML>
> >
> > ------=_NextPart_000_016C_01C2C1FE.07ADCD80--
> >
> >
>
>
| 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 |