Re: Oracle equivalent of the MSSQL row_number() function

> I have an Oracle 9i table containing details of "courses taken":

Hmmmm, another homework. OK, I am in the right mood.
>

>
> 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

I doubt that SQL Server would require such a clumsy construct. This query would also be perfectly legal in the Oracle world. Please note that logic is deeply flawed. You are ordering the grades and selecting only the first rows, instead of just selecting the maximum.

>
> But what do I use in Oracle?
>
> Thanks.

This looks like a far better solution:

WITH CTE AS (
  SELECT
    CourseCode,
    StudentID,
    DateAttended,
    Grade,
    max(Grade) OVER(PARTITION BY StudentID,CourseCode) as MaxGrade   FROM Courses
)
SELECT CourseCode, StudentID, DateAttended, Grade FROM CTE
WHERE Grade=MaxGrade

BTW, this group is not really meant for solving your homework. You should do your own homework.

-- 
http://mgogala.byethost5.com