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,Comments) VALUES (1,1,55,1,'Satisfactory');

1 row created.

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

1 row created.

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

1 row created.

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

1 row created.

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

1 row created.

SQL>
SQL>
SQL> SELECT StudentID, AverageMark FROM (
  2     SELECT StudentID, AVG(Mark) AS 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

2 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

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

Related examples in the same category