/*-------------------------------------------------*/
/* Difference between ROWNUM and ROW_NUMBER()      */
/* ROWNUM       is assigned before ORDER BY        */
/* ROW_NUMBER() is assigned after query completion */
/*-------------------------------------------------*/

/* Notice that ROWNUM is assigned before the ORDER BY */

SELECT ROWNUM, s.*, c.* 
FROM   student s join class c
ON     ssn=stu_ssn
ORDER BY lname;

SELECT ROWNUM, s.*, c.* 
FROM   student s join class c
ON     ssn=stu_ssn
WHERE  rownum <= 10 
ORDER BY lname;


/* Notice that ROW_NUMBER() is assigned after the ORDER BY */

SELECT ROWNUM, ROW_NUMBER() OVER(ORDER BY lname) as row_number, s.*, c.* 
FROM   student s join class c
ON    ssn=stu_ssn
ORDER BY lname;

SELECT * 
FROM 
(
    SELECT ROW_NUMBER() OVER(ORDER BY lname) as row_number, s.*, c.* 
    FROM   student s join class c
    ON     ssn=stu_ssn
    ORDER BY lname
)
WHERE row_number <= 10;