returning most recent value for each of many different classes of data in one SQL query?

Right now I have a query that returns the largest value for multiple 'classes' of data. I'm trying to figure out what to change so that my query will return the most recent value for each of the distinct classes below.

The data itself is a large table of student grades. A student can have grades in multiple terms (captured by the pgf.finalgradename field). For most students simply joining by the student identifier and the class identifier will return the correct data; however, I'm trying to account for situations where a student drops one section of a course and enrolls in another section (of the same course). For my purposes, we can assume that the most recently updated grade represents the preferred record.

In addition to the fields below there is a pgf.lastgradeupdate field that represents the date the record was updated. In English, what I'm hoping to get back is the most recent row based on pgf.lastgradeupdate. Given this data:

studentid     course_number     finalgradename     percent     lastgradeupdate
100           M900              H1                 70          01-OCT-10
100           M900              H1                 90          20-OCT-10
100           M900              H2                 85          01-JAN-11
100           M900              H3                 88          06-FEB-11
100           M900              H4                 89          07-JUN-11

I would want the query to return:

studentid     H1     H2     H3     H4
100           90     85     88     89

Here's what I have:

select cc.studentid, 
max(case when pgf.finalgradename='H1' then pgf.percent else null end) as H1,
max(case when pgf.finalgradename='H2' then pgf.percent else null end) as H2,
max(case when pgf.finalgradename='H3' then pgf.percent else null end) as H3,
max(case when pgf.finalgradename='H4' then pgf.percent else null end) as H4
from cc

left outer join sections sect on abs(cc.sectionid) = sect.id
left outer join courses on sect.course_number = courses.course_number
left outer join pgfinalgrades pgf on cc.studentid = pgf.studentid and abs(cc.sectionid) = pgf.sectionid

where abs(cc.termid) >= 2000 and cc.course_number = 'M900'
group by cc.studentid

Do I need to make a subquery for each of those 'classes' of data? Or do I need to do a bunch of self joins? The other questions I read here only seemed to be based off of situations where only one date needed to be considered - not several. T开发者_如何学编程hanks!

How close to what you want is this?

create table your_data(
  studentid       number
 ,course_number   varchar2(4)
 ,finalgradename  varchar2(2)
 ,percent         number
 ,lastgradeupdate date
);

insert into your_data values(100, 'M900', 'H1', 70, date '2010-10-01');
insert into your_data values(100, 'M900', 'H1', 90, date '2010-10-20');
insert into your_data values(100, 'M900', 'H2', 85, date '2011-01-01');
insert into your_data values(100, 'M900', 'H3', 88, date '2011-02-06');
insert into your_data values(100, 'M900', 'H4', 89, date '2011-06-07');

commit;


select studentid
      ,course_number
      ,max(case when finalgradename = 'H1' then percent end) as h1
      ,max(case when finalgradename = 'H2' then percent end) as h2
      ,max(case when finalgradename = 'H3' then percent end) as h3
      ,max(case when finalgradename = 'H4' then percent end) as h4
  from (select studentid
              ,finalgradename
              ,course_number
              ,percent
              ,row_number() over(partition by studentid
                                             ,course_number
                                             ,finalgradename 
                                     order by lastgradeupdate desc) as rn
          from your_data
        )
 where rn = 1
 group 
    by studentid
      ,course_number;

 STUDENTID COUR         H1         H2         H3         H4
---------- ---- ---------- ---------- ---------- ----------
       100 M900         90         85         88         89

Updated query: I inserted your query inside mine. I can't test it though...Let me know if it works!

select studentid
      ,course_number
      ,max(case when finalgradename = 'H1' then percent end) as h1
      ,max(case when finalgradename = 'H2' then percent end) as h2
      ,max(case when finalgradename = 'H3' then percent end) as h3
      ,max(case when finalgradename = 'H4' then percent end) as h4
  from (select cc.studentid
              ,pgf.finalgradename
              ,courses.course_number
              ,pgf.percent
              ,row_number() over(partition by cc.studentid
                                             ,courses.course_number
                                             ,pgf.finalgradename 
                                     order by lastgradeupdate desc) as rn
          from cc
    left outer 
          join sections sect     on abs(cc.sectionid) = sect.id
    left outer 
          join courses           on sect.course_number = courses.course_number
    left outer 
          join pgfinalgrades pgf on (cc.studentid = pgf.studentid and 
                             abs(cc.sectionid) = pgf.sectionid)
         where abs(cc.termid) >= 2000 and cc.course_number = 'M900'
        )
 where rn = 1
 group 
    by studentid
      ,course_number;