Re: agregates - Mailing list pgsql-general
| From | codeWarrior |
|---|---|
| Subject | Re: agregates |
| Date | |
| Msg-id | b0mp22$14fu$1@news.hub.org Whole thread Raw |
| In response to | agregates ("David Blood" <david@matraex.com>) |
| List | 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@adelphia.net> wrote in message news:b0mo75$11ne$1@news.hub.org... > SELECT paymentid, customerid FROM tblpayment where paymentdebit > 0 LIMIT 1 > OFFSET 1 > > > > ""David Blood"" <david@matraex.com> wrote in message > news:016b01c2c238$b40ca580$1f00a8c0@redwood... > > 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-- > > > > > >
pgsql-general by date: