Retrieving the Top Five Students with ROWNUM : ROWNUM « Select Query « Oracle PL / SQL

Home
Oracle PL / SQL
1.Aggregate Functions
2.Analytical Functions
3.Char Functions
4.Constraints
5.Conversion Functions
6.Cursor
7.Data Type
8.Date Timezone
9.Hierarchical Query
10.Index
11.Insert Delete Update
12.Large Objects
13.Numeric Math Functions
14.Object Oriented Database
15.PL SQL
16.Regular Expressions
17.Report Column Page
18.Result Set
19.Select Query
20.Sequence
21.SQL Plus
22.Stored Procedure Function
23.Subquery
24.System Packages
25.System Tables Views
26.Table
27.Table Joins
28.Trigger
29.User Previliege
30.View
31.XML
Oracle PL / SQL » Select Query » ROWNUM 
Retrieving the Top Five Students with ROWNUM
   
SQL>
SQL> CREATE TABLE SAT (
  2     StudentID  INT NOT NULL,
  3     ExamID     INT NOT NULL,
  4     Mark       INT,
  5     IfPassed   SMALLINT,
  6     Comments   VARCHAR(255),
  7     CONSTRAINT PK_SAT PRIMARY KEY (StudentID, ExamID));

Table created.

SQL>
SQL>
SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,CommentsVALUES (1,1,55,1,'Satisfactory');

row created.

SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,CommentsVALUES (1,2,73,1,'Good result');

row created.

SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,CommentsVALUES (2,3,44,1,'Hard');

row created.

SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,CommentsVALUES (2,5,39,0,'Simple');

row created.

SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassedVALUES (2,6,63,1);

row created.

SQL>
SQL>
SQL> SELECT StudentID, AverageMark FROM (
  2     SELECT StudentID, AVG(MarkAS AverageMark
  3     FROM SAT
  4     GROUP BY StudentID
  5     ORDER BY AverageMark DESC
  6  )
  7  WHERE ROWNUM <= 5;

 STUDENTID AVERAGEMARK
---------- -----------
         1          64
         2  48.6666667

rows selected.

SQL>
SQL> drop table SAT;

Table dropped.

SQL>

   
    
    
  
Related examples in the same category
1.Use rownum in select clause
2.Use rownum in update set statement
3.Use rownum in where clause to control the row count
4.Use rownum column with order by
5.Use rownum in where clause to limit the row count
6.Limit the query to display only the top 3 highest paid employees.
7.Use rownum to limit the subquery
8.Use rownum = 1 and select into
9.Getting the Five Most Expensive Products
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.