Select field based on other column max value in oracle pl/sql

I am calculating a field called “Degree Level” in a view. This is a field in the table “Degrees”, and the table shows degrees for each faculty member. A faculty member can have more than one degree.

The field “degree level” is also in the table “Crosswalk_Table”. I want to choose Degree level for a faculty member based on the max value in the column “Degree_Hierarchy” in the Crosswalk_table.

The code below displays the “Master” instead of the “Doctor” for degree level (which has the higher hierarchy value). ANY help is much appreciated thank you.

CAST (                                                                        
       (SELECT DEGREE_LEVEL
          FROM Degrees D, Crosswalk_Table E
         WHERE     
                E.DEGREE_HIERARCHY =
                (SELECT MAX (DEGREE_HIERARCHY)
                    FROM Crosswalk_Table 
                    WHERE DEGREE_CODE = D.FACULTY_DEGREE_CODE)
               AND D.FACULTY_DEGREE_CODE = E.DEGREE_CODE
               AND D.PERSON_SKEY = SRC.PERSON_SKEY
               AND ROWNUM <=1
               ORDER BY DEGREE_HIERARCHY DESC)                 
   AS VARCHAR2 (50))

Sample Data:

Degree table:

Person_skey  Degree_Code 
-------------------------
123456      MA           

123456      JD

Crosswalk_Table:

degree_level  degree_code  degree_hierarchy
---------------------------------------------
master        MA           30

doctor        JD           40

Answer

If you are using Oracle 12 or higher, then you may use a subquery like that (with ORDER BY and LIMIT to 1 row):

SELECT c.DEGREE_LEVEL
FROM Degrees d
JOIN CROSSWALK_TABLE c
ON c.Degree_Code = d.Degree_Code
WHERE d.Person_skey = 123456
ORDER BY c.DEGREE_HIERARCHY DESC
FETCH FIRST  ROW ONLY

Please take a look at this simple demo: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=c8d41924c593f4f361de59a611a363cc