Oracle. How to return correct record based on field values?

Question

Oracle

There is a select that pulls out a record based on the maximum value of the 1st full

SELECT *
      FROM
      (
        SELECT discount, osp_id, sero_id, estpt_id, 
               ROW_NUMBER() OVER 
              (PARTITION BY osp_id, sero_id, estpt_id ORDER BY festpae_id DESC) as festpae_id 
          FROM database1 data1
          JOIN database2 data2 ON data2.id = data1.attr_id
         WHERE data1.interest_rate = 1 
           AND data1.ordet_id = data2.id
           AND data1.objt_attr_id = data2.objt_attr_id 
       )
      WHERE festpae_id = 1

List in a row:

DISCOUNT     OSP_ID    SERO_ID   ESTPT_ID FESTPAE_ID   VALUE
    ---------- ---------- ---------- ---------- ---------- ----------
          50     619356    3931831       2144    NULL       0
          40     619356    3931831       2144    NULL       1
          30     619356    3931831       2144    2000743    0
          15     619356    3931831       2144    2000744    1

Performing a select, I get this

DISCOUNT     OSP_ID    SERO_ID   ESTPT_ID FESTPAE_ID   VALUE
    ---------- ---------- ---------- ---------- ---------- ----------
          50     619356    3931831       2144    NULL       0

My knowledge is not great, so I turn to you.

I need to take into account some more fields

1. If there are entries where `festpae_id is NULL`, then a check for `festpae_id is NULL` and `value = 1` is needed, so it should return an entry where discount 40 (in this case)
   
2. If there are no entries where `festpae_id is null`, then it should look at the maximum `(festpae_id)` (I use ROW_NUMBER() in this case) and `value = 1`.
   
3. In all other cases, it must return void.
   

I know that you can use DECODE (), but then it all turns into horror. Can someone help? I have been thinking about this for more than 5 hours and nothing...

PS This select is used in the view table as `LEFT OUTER JOIN`.

Answer 1

You do not try to solve this problem head-on. Look, your bottleneck is related to the last condition. Divide it into two conditions, the first one takes the maximum of the values, or if the value is null, then search by the second condition.
It is important to understand here that they would not work for you at the same time. Another option is to wrap this request, if you have it in a function, of course.