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:

Previous
From: Timur Irmatov
Date:
Subject: types & index usage
Next
From: "Frankie"
Date:
Subject: Is there a way knowing which database I'm connecting to?