Re: getting the top 10 rows from a query

<mark_roberts_at_stercomm.com> wrote in message news:1120781358.001908.133090_at_g44g2000cwa.googlegroups.com...
>I have found several solutions close to what I want, but I haven't been
> able to figure this out yet. We have a table ("Documents") that we
> want to group by Sender, order by the count of the documents in
> descending order and then return only the top 10 rows - basically the
> 10 senders who sent us the most documents.
>
> CREATE TABLE DOCUMENTS (
> DOCUMENTID NUMBER (10) NOT NULL,
> SENDERID NVARCHAR2 (50),
> DOCUMENTDATETIME DATE);
>
> The code below works from SQL+, but it won't even compile from within a
> stored procedure - also shown.
>
> works in SQL+......
> SELECT Sender, DocCount
> FROM (SELECT SenderID AS Sender,
> count(*) AS DocCount
> FROM Documents
> WHERE BusinessID = 1
> AND DocumentDateTime > SYSDATE - 30
> GROUP BY SenderID
> ORDER BY count(*) DESC)
> WHERE ROWNUM <= 10;
>
> won't compile in a stored proc.......
> OPEN pTPInfocursor FOR
> SELECT Sender, DocCount
> FROM (SELECT SenderID AS Sender,
> count(*) AS DocCount
> FROM Documents
> WHERE BusinessID = 1
> AND DocumentDateTime > SYSDATE - 30
> GROUP BY SenderID
> ORDER BY count(*) DESC)
> WHERE ROWNUM <= 10;
>
> The question is am I doing something wrong from the stored procedure &
> can it be fixed? I've tried a lot of things, but I'm fairly new to
> Oracle and can't get it right. If it won't work like this (I've seen
> some posts that kind of say this won't work), then how can I achieve my
> goal?
>
> One idea I had was to use 2 cursors. Fill 1 cursor with the inner
> select and then loop through and fetch the first 10 rows and put them
> in another cursor, but I can't find any ways to "fill" a cursor without
> using select.
>
> I wouldn't think this is too hard, but I can't find any good
> resolutions. Any help would be appreciated. Thanks.
>
> Mark Roberts
> Sterling Commerce
> Dublin, OH USA
>

the PL/SQL SQL engine isn't as complete as the standard SQL engine - this is (I suspect) the reason why it isn't working.

One obvious solution would be to create a proper view for the inline select then reference that view in your code. Received on Fri Jul 08 2005 - 13:19:09 CDT