getting the top 10 rows from a query

From: <mark_roberts_at_stercomm.com>
Date: 7 Jul 2005 17:09:18 -0700
Message-ID: <1120781358.001908.133090@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 Received on Thu Jul 07 2005 - 19:09:18 CDT