Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: How to get the 'top ten' from a SELECT count(*)
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