Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: How to get the 'top ten' from a SELECT count(*)

Re: How to get the 'top ten' from a SELECT count(*)

From: Carsten Jacobs <carsten.jacobs_at_tool42.com>
Date: 2000/04/26
Message-ID: <3906E272.A8F5D230@tool42.com>#1/1

Sorry to ask you again but

I can't create a view on it because I execute the select .. where rownum<11
in a loop where another cursor delivers values I use in the mentioned select. I tried to
create the view for each cycle in the outer cursor loop but it looked like that DDL Statements are
not allowed in procedures.
Then I tried to create a cursor for the statement but I got an error message about the ORDER BY clause
PLS-00103: Encountered the symbol "ORDER" when expecting one of the following:

   . ( ) , * @ % & - + / mod rem xor <> oder != oder ~= In

If I drop the ORDER BY clause, it will be compiled successfully. But that's not what I need.

If you have any idea, please let me know. But thank you anyway

Carsten

create ....
is
 CURSOR ....  CURSOR crsrStartTime IS
  select * from (
  SELECT count(*) amount, country
  FROM customer
  WHERE regdate = ldate --variable comes from another cursor   GROUP by customer
  ORDER by amount
  )
  where rownum < 11;
begin
  :
end ...;
Connor McDonald schrieb:
>
> I haven't checked but possibly try creating a view of
> the select portion and then code
>
> insert into ...
> select * from view;
>
> in the procedure.
>
> Cheers
> Connor
>
> --- Carsten Jacobs <carsten.jacobs_at_tool42.com> wrote:
> > thank you very much for this advice.
> >
> > There is just one question left for me:
> > I want to use this construction in a stored
> > procedure like
> >
> > create ....
> > is
> > begin
> > insert into another_table
> > select * from (
> > SELECT count(*) amount, country
> > FROM customer
> > GROUP by customer
> > ORDER by amount
> > )
> > where rownum < 11;
> > commit;
> > end ...;
> >
> > When I test the insert ... select in sqlplus it
> > works fine but I can't
> > compile it as an procedure?
> > Do you have an answer for this?
> > If so, it would be very nice to hear from you. Many
> > thanks in advance
> >
> > Carsten
> >
> > Connor McDonald schrieb:
> > >
> > > Carsten Jacobs wrote:
> > > >
> > > > Hello!
> > > >
> > > > I want to make something like a top ten list.
> > > > Does anybody hava a smart idea to get only the
 first ten rows of a
> > > > select like
> > > >
> > > > SELECT count(*) amount, country
> > > > FROM customer
> > > > GROUP by customer
> > > > ORDER by amount
> > > >
> > > > Thanks for any help
> > > >
> > > > Carsten
> > >
> > > select * from (
> > > SELECT count(*) amount, country
> > > FROM customer
> > > GROUP by customer
> > > ORDER by amount
> > > )
> > > where rownum < 11
> > >
> > > for 8i and above. There are also new analytic
 functions available from
> > > 8i.2 onwards.
> > >
> > > HTH
> > > --
> > > ===========================================
> > > Connor McDonald
> > > http://www.oracledba.co.uk
> > >
> > > We are born naked, wet and hungry...then things
> > get worse
> >
> > --
> >
> > The information transmitted is intended only for the
> > person or entity to
> > which it is addressed and may contain confidential
> > and/or privileged
> > material. Any review, retransmission, dissemination
> > or other use of, or
> > taking of any action in reliance upon, this
> > information by persons or
> > entities other than the intended recipient is
> > prohibited. If you received this in error, please
> > contact the sender and
> > delete the material from any computer.
> >
>
> __________________________________________________
> Do You Yahoo!?
> Send online invitations with Yahoo! Invites.
> http://invites.yahoo.com
Received on Wed Apr 26 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US