Re: getting the top 10 rows from a query

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Sun, 10 Jul 2005 02:13:49 GMT
Message-Id: <pan.2005.07.10.02.13.48.310681@sbcglobal.net>

On Thu, 07 Jul 2005 17:09:18 -0700, mark_roberts wrote:

> 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.......

Try this:

select distinct senderid, count(*) over (partition by senderid)

                          as doccount 

from documents
where businessid=1
order by doccount;

It works within a a procedure, too:

SQL> create or replace procedure tst
  2 as
  3 cursor csr is select distinct c1,count(c1) over (partition by c1) as cnt

  4                from abc
  5                order by cnt;

  6 begin
  7 for c in csr loop
  8 dbms_output.put_line(c.c1||' '||c.cnt);   9 end loop;
 10 end;
 11 /

Procedure created.

SQL> exec tst;
1 3
2 3
3 4

PL/SQL procedure successfully completed.

SQL> Table ABC looks like this:
SQL> select * from abc;

        C1 C2
---------- -----

         1 A
         1 A
         2 A
         2 C
         2 D
         3 A
         3 E
         3 F
         3 G
         1 Z

10 rows selected.

-- 
http://www.mgogala.com

Received on Sat Jul 09 2005 - 21:13:49 CDT