Oracle query to get single row with the maximum value

Question

SELECT DISTINCT cfm.student_id,
                cfm.first_name,
                cfm.last_name,
                ss.school_name,
                se.grade_level
  FROM ce_family_member@dblink_sme cfm
  JOIN st_enrollment@dblink_sme se
    ON se.student_id = cfm.student_id
  JOIN sy_school@dblink_sme ss
    ON ss.school_id = se.school_id
 WHERE cfm.student_id IN
       (SELECT cfm.student_id
          FROM sec_user@dblink_sme su
          JOIN ce_house_head@dblink_sme chh
            ON chh.house_head_id = su.party_id
          JOIN ce_family_to_member@dblink_sme cfm
            ON cfm.family_id = chh.family_id
         WHERE su.login_name = 'johnmccormick@homerschools')
 ORDER BY first_name DESC, grade_level DESC

And this gives me the output as follows

What I want is only to display the student details with the highest grade level as following

3332    Pat OKAMOTO     Delta Jr High School    8
7407    Antoine HOSLEY  Delta Jr High School    8

Answer 1

From Oracle 12, you can use:

SELECT your_columns
FROM   your_query
ORDER BY grade_level DESC
FETCH FIRST ROW WITH TIES;

In earlier versions, you can use:

SELECT your_columns
FROM   (
  SELECT your_columns,
         DENSE_RANK() OVER (ORDER BY grade_level DESC) AS rnk
  FROM   your_query
)
WHERE  rnk = 1;