title: Oracle. Return correct record based on fields values
body: <p>From the previous post, someone was already helpful and helped me out to create this select which returns 1 record by max. <code>festpae_id</code> </p>
  
  <pre><code>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
  </code></pre>
  
  <p>Records:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>So from that select, I will get this record</p>
  
  <pre><code>DISCOUNT     OSP_ID    SERO_ID   ESTPT_ID FESTPAE_ID   VALUE
  ---------- ---------- ---------- ---------- ---------- ----------
        50     619356    3931831       2144    NULL       0
  </code></pre>
  
  <p>For now, I'm having more conditions witch should be checked and my mind has blown out, plus my oracle knowledge is not good enough.</p>
  
  <p>So what do I need? As you can see there are 2 columns called <code>value</code> and <code>festpae_id</code>. They must be taken into condition. </p>
  
  <ol>
  <li>If records exist where <code>festpae_id is NULL</code>, then he should check all records where <code>festpae_id is NULL</code> and <code>value = 1</code> and return record if exists.</li>
  <li>If there is no records where <code>festpae_id is null</code>, then select should return the record with <code>MAX(festpae_id)</code> (in this case ROW_NUMBER() is used) and where <code>value = 1</code>.</li>
  <li>In other cases, select should return nothing.</li>
  </ol>
  
  <p>I know, that it could be done via <code>DECODE()</code>, but then I need to copy-paste the code witch is not OK. Maybe someone of you could be so kind and share his thoughts about the solution? Working on this for at least 5 hours now and can't find a way how to make it.</p>
  
  <p>P.S This select is used in the view table as <code>LEFT OUTER JOIN</code>.</p>