Re: How to select top n rows from a table?

> I find Oracle privides ROWNUM psuedocolumn but the ORDER BY
> clause cannot be used in any subqueries. Do you know how to solve this
> problem? Please give me a hand.

SQL> select * from students;

NAME                SCORE
--------------- ---------
Joe                    50
Fred                   20
Jane                   70
Steve                  90
Russ                  100
Jake                   15


SQL> create view student_view as select score, name 
  2                              from students 
  3                              group by score, name;

View created.

SQL> select rownum, name, score from student_view;

   ROWNUM NAME                SCORE
--------- --------------- ---------
        1 Jake                   15
        2 Fred                   20
        3 Joe                    50
        4 Jane                   70
        5 Steve                  90
        6 Russ                  100

SQL> select rownum, name, score
  2 from student_view
  3 where rownum < 4;

   ROWNUM NAME                SCORE
--------- --------------- ---------
        1 Jake                   15
        2 Fred                   20
        3 Joe                    50

Group by may be 'cheating', but since students should be unique it should work.... Received on Mon Apr 05 1999 - 22:19:11 CDT