Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> 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
![]() |
![]() |