How to fetch max grade for same course in different academic session

Question

I am working on PEOPLESOFT CMS . Students enroll in courses and receive grades. Sometimes a student repeats a course and gets a better grade. I need to calculate the GPA by using only the best grade. So for each student that repeats courses, I have to determine what the highest grade is.E.g. in 2nd semester he got 3 grade_point but in in 4 semester he improved to 4.5 grad_point then query should fetch 4.5 instead of 3 when we notify result for all 4 semester but when we notify for upto 2nd semester its could return 3 grad_point.here i construct a sql which working fine in selection bt when i make it view then i got a problem for handling it different semester its always return max for 4th semester result bt in view its missed the course upto 2 semester gpa here is query

SELECT DISTINCT A.institution 
 ,A.acad_career 
 ,A.emplid 
 ,a.crse_id 
 ,A.UNT_TAKEN 
 , a.acad_prog 
 ,first_value(a.grade_points) OVER (PARTITION BY A.emplid 
 ,a.crse_id 
  ORDER BY a.grade_points DESC) AS GPA 
  ,first_value(a.strm) OVER (PARTITION BY A.emplid 
  ,a.crse_id 
  ORDER BY a.grade_points DESC) AS strm 
  FROM ps_qau_maxgp_ugrd a 
 WHERE acad_career='UGRD' 
 AND Emplid LIKE '04091313014%'
 AND Strm='1313'
  ORDER BY A.institution ,A.acad_career ,A.emplid  ,A.UNT_TAKEN , a.acad_prog

Answer 1

Just use MAX and GROUP BY.

SELECT A.institution, 
       A.acad_career, 
       A.emplid, 
       a.crse_id, 
       A.unt_taken, 
       a.acad_prog, 
       Max(a.grade_points) GPA, 
       Max(a.strm)         AS strm 
FROM   ps_qau_maxgp_ugrd a 
WHERE  acad_career = 'UGRD' 
       AND emplid LIKE '04091313014%' 
       AND strm = '1313' 
GROUP  BY A.institution, 
          A.acad_career, 
          A.emplid, 
          a.crse_id, 
          A.unt_taken, 
          a.acad_prog;

Answer 2

I believe you need to look at the date the grade was entered. For a given term and class-nbr, you should pull the greatest grade: pseudo code:

select 
   max(date_entered) 
group by 
   institution, 
   acad_career,
   crse_id, 
   term,
   class_nbr(if you have it) 

The problem is that with the table you are using, i don't beleive is the actual enrollment table. I am only familiar with CS, so i would be looking at the ps_stdnt_enrl table or the ps_stdnt_car_term to get the accumulated data for a term.