Re: TOP TEN LISTS? HELP.

From: kenneth d atkins, s=david m thomas <Kenneth.D.Atkins_at_tek.com>
Date: 16 Dec 1994 22:01:05 GMT
Message-ID: <3ct2n1$hp2_at_goodnews.wv.tek.com>

Sunil Mushran <smushran_at_us.oracle.com> wrote:
>
> Actually it is basic...that is, if I got you correct!
>
> select * from table where rownum<11;
>
> will get you the first 10 rows....
>
> mwmaher_at_ACM.ORG wrote:
> >
> > How do you create a listing of the top N items in a table
> > with straight SQL? This seems like such a basic task -
> > until you try it.
> >
> > Thank you in advance.
>

WARNING: This method will no work if you try it with a join that is ordered by a specific column. ORACLE creates the ROWNUM before ordering the output of the join, so the 'where rownum<11' clause will not return the first 10 rows in your ordered join. Example:

    TABLE   T2:                   TABLE T1:

      COL1       COL2                 COL1
---------- ----------             --------
         1         90                    1
         1         80                    2
         1         70                    3
         1         50                    4
         2         50                    5
         2         70
         2         80
         2         90
         3         90
         3         70
         4         90
         4         80
         5         90

select rownum,t1.col1, t2.col2
  from t1, t2
 where t1.col1 = t2.col1
 order by t2.col2

    ROWNUM COL1 COL2
---------- ---------- ----------

         4          1         50
         5          2         50
         3          1         70
        10          3         70
         6          2         70
         2          1         80
        12          4         80
         7          2         80
         1          1         90
         8          2         90
        13          5         90
        11          4         90
         9          3         90

So the 'where rownum<11' clause would miss the 7th row'

---

Kenneth Atkins
Financial Data Systems Incorporated (FDSI) Beaverton, OR
email: Kenneth.D.Atkins_at_tek.com (until 2/95)

---

Received on Fri Dec 16 1994 - 23:01:05 CET