Subquery - Getting The Highest Score

I am trying to get the student that scored highest on the final exam first I select SELECT s.STUDENT_ID, w.LAST_NAME,w.FIRST_NAME, MAX(s.NUMERIC_GRADE) AS NUMERIC_FINAL_GRADE FROM

Solution 1:

The traditional method is an analyticMAX() (or other analytic function):

select *
  from ( select s.student_id
              , w.last_name
              , w.first_name
              , s.numeric_grade
              , max(s.numeric_grade) over () as numeric_final_grade
           from grade s
           join section z
             on s.section_id = z.section_id
           join student w
             on s.student_id = w.student_id
          where z.course_no = 230and z.section_id = 100and s.grade_type_code = 'FI'
                )
 where numeric_grade = numeric_final_grade

But I would probably prefer using FIRST (KEEP).

selectmax(s.student_id) keep (dense_rank firstorderby s.numeric_grade desc) as student_id
     , max(w.last_name) keep (dense_rank firstorderby s.numeric_grade desc) as last_name
     , max(w.first_name) keep (dense_rank firstorderby s.numeric_grade desc) as first_na,e
     , max(s.numeric_grade_name) as numeric_final_grade
  from grade s
  join section z
    on s.section_id = z.section_id
  join student w
    on s.student_id = w.student_id
 where z.course_no =230and z.section_id =100and s.grade_type_code ='FI'

The benefits of both of these approaches over what you initially suggest is that you only scan the table once, there's no need to access either the table or the index a second time. I can highly recommend Rob van Wijk's blog post on the differences between the two.

P.S. these will return different results, so they are slightly different. The analytic function will maintain duplicates were two students to have the same maximum score (this is what your suggestion will do as well). The aggregate function will remove duplicates, returning a random record in the event of a tie.

Solution 2:

SELECT * FROM 
(
  SELECT s.STUDENT_ID, w.LAST_NAME,w.FIRST_NAME, 
     MAX(s.NUMERIC_GRADE) AS NUMERIC_FINAL_GRADE
  FROM GRADE s, SECTION z, STUDENT w
  WHERE s.SECTION_ID = z.SECTION_ID 
     AND s.STUDENT_ID = w.STUDENT_ID
     AND z.COURSE_NO = 230AND z.SECTION_ID = 100AND s.GRADE_TYPE_CODE = 'FI'GROUPBY s.STUDENT_ID, w.FIRST_NAME, w.LAST_NAME
  ORDERBY MAX(s.NUMERIC_GRADE)
) AS M 
WHERE ROWNUM <= 1