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

MT0 · Accepted Answer · 2022-11-15 16:21:52Z

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;

answered Nov 15, 2022 at 16:21

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vishakh .K.V Over a year ago

Hi MT0, how can I apply this to the query I mentioned?

MT0 Over a year ago

@Vishakh.K.V Take your query and replace the ORDER BY ... clause on the last line of your query with the ORDER BY ... FETCH FIRST ROW WITH TIES last 2 lines from my first query.

MT0 Over a year ago

Alternatively, replace SELECT your_columns with the SELECT clause from your query and FROM your_query with the FROM and WHERE clauses from your query.

Collectives™ on Stack Overflow

Oracle query to get single row with the maximum value

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related