Oracle equivalent of the MSSQL row_number() function

CourseCode, StudentID, DateAttended, Grade

0001, 1, 2011/04/18, 60
0001, 1, 2011/05/04, 80
0001, 2, 2011/02/08, 65
0002, 1, 2011/05/12, 75
0002, 2, 2011/05/12, 75
0002, 2, 2011/05/15, 95
0003, 1, 2011/03/22, 75
0003, 1, 2011/03/25, 95
0003, 2, 2011/11/02, 45
0003, 2, 2011/11/05, 90

A student can do a course more than once, so there can be more than one record with the same CourseCode per student.

How do I select the record with the highest grade for each course for each student? I want this result:

CourseCode, StudentID, DateAttended, Grade

0001, 1, 2011/05/04, 80
0001, 2, 2011/02/08, 65
0002, 1, 2011/05/12, 75
0002, 2, 2011/05/15, 95
0003, 1, 2011/03/25, 95
0003, 2, 2011/11/05, 90

In SQL server I would use row_number(), example: WITH CTE AS (
  SELECT
    CourseCode,
    StudentID,
    DateAttended,
    Grade,
    rowno = row_number() OVER(PARTITION BY CourseCode ORDER BY DateAttended DESC)
  FROM Courses
)
SELECT CourseCode, StudentID, DateAttended, Grade FROM CTE
WHERE rowno = 1

But what do I use in Oracle?

Thanks. Received on Sun Aug 21 2011 - 11:56:12 CDT