Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to select top n rows from a table?

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

From: Michael Myers <mjmyers_at_blazenet.net>
Date: Mon, 05 Apr 1999 23:19:11 -0400
Message-ID: <37097D2F.E0145D2C@blazenet.net>


> 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US