Oracle equivalent of the MSSQL row_number() function
From: luisdev <andyza_at_webmail.nospam.co.za>
Date: Sun, 21 Aug 2011 18:56:12 +0200
Message-ID: <s1e2571fgi5fuf6fnii9rip44bp54eu6rq_at_4ax.com>
I have an Oracle 9i table containing details of "courses taken":
Date: Sun, 21 Aug 2011 18:56:12 +0200
Message-ID: <s1e2571fgi5fuf6fnii9rip44bp54eu6rq_at_4ax.com>
I have an Oracle 9i table containing details of "courses taken":
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