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.

On Sun, 21 Aug 2011 18:56:12 +0200, luisdev wrote:

Hmmmm, another homework. OK, I am in the right mood.
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.


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

On Aug 21, 7:12*pm, Mladen Gogala <gogala.mla... (AT) gmail (DOT) com> wrote:
group...
It's not "homework"! I've been out of "school" for a long time :->

What groups are more suited to PL/SQL questions?

Thanks again for your response...

On Aug 21, 10:33*am, luisdev <and... (AT) webmail (DOT) co.za> wrote:
There are several groups associated with comp.databases.oracle.*.
They are: server, misc, tools and marketplace. The groups are defined
in the charter, available http://www.dbaoracle.net/readme-cdos.htm#subj3.
This arrangement came about because one old group called cdo was
getting a bit overwhelmed. People aren't too picky about following
the charter (except for commercial postings), since many read all the
non-marketplace groups, especially now that there are so many other
places to go that usenet has been on life-support. But still, many
people lurk and will come out of the woodwork with an interesting
question, and some of us are still regulars here. Technically,
language questions usually are associated with tools, while dba
questions would be server, but it really makes more of a difference
that the question is well written.

But of course, your question is not a PL/SQL question (and if it were,
the answer would be to use SQL). It can be a bit confusing, but the
difference is procedural v. non-procedural. In general, a relational
database can work better with non-procedural constructs based on set
theory. Some people coming from t-sql want to do everything
procedurally, and it is often hard to convince them that may not be
the best. Hard to tell from one question, but the fact that you used
this type of syntax shows you are one step ahead of the pack,
regardless of Mladen's judgment of one piece of code. But he gave you
a better answer, which is always a positive sign in these groups. We
do see a lot of homework questions similar to yours here, especially
this time of year, so don't let that bother you.

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...ts-wider-loss/