Max Grade_points for Repeated Course by Student

Question

I am working on a student enrollment database project. Students enroll in courses and receive grades. Sometimes a student repeats a course and gets a better grade. I need to calculate the sum of grade_point and credit by using only the best grade. So for each student that repeats courses in different semester, I have to determine what the highest grade is. emplid represents the student, course_id identifies a course,credit is credits hr of course, Grade_point is the numeric value of the letter grade, and term represents the semester session.

Here is an example of what I am trying to accomplish.

emplid couse_id credit_hr grade_pt term    
  0001    6001       3        4      Fall15 
  0001    6002       3       3.5     Fall15
  0001    6003       3        2      Fall15
  0001    6004       4       2.5     Sp16
  0001    6002       3       3.0     Sp16 
total(requirrd)      13      12

Sample code from OP's comment:

SELECT a.emplid
      ,a.subject
      ,a.CATALOG_NBR
      ,a.strm
      ,a.CRSE_GRADE_OFF
      ,a.R‌​EPEAT_CANDIDATE
      ,a.un‌​t_taken AS cr
      ,a.CRSE_ID
      ,MAX(a.grade_points)
 OVER (PARTITION BY A.emplid ,crse_id) 
 FROM ps_CLASS_TBL_SE_VW a
 WHERE emplid LIKE '06381313011%'

Answer 1

select emplid, sum(credit_hr), sum(grade_pt)
from (
select emplid, course_id, credit_hr, grade_pt, term,
row_number() over(partition by emplid, course_id
order by grade_pt desc) rn
from your_table
)
where rn = 1
group by emplid