title: MAX function in oracle not pulling back expected results
body: <p>I have table which has a bunch of data. A sample of the data is as <em>emphasized text</em>follows:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>DTE</th>
  <th>UNIQUE_ID</th>
  <th>ROW_DATA</th>
  <th>CALL_ROW_ID</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>8/1/2021</td>
  <td>11234</td>
  <td><code>DCNS=FAILED\|HYPO=</code></td>
  <td>1</td>
  </tr>
  <tr>
  <td>8/1/2021</td>
  <td>11234</td>
  <td><code>DCNS=ACCEPTED \| HYPO=NEW CREDIT</code></td>
  <td>2</td>
  </tr>
  <tr>
  <td>8/1/2021</td>
  <td>11234</td>
  <td><code>DCNS=FAILED\|HYPO=GIVE ME BIRTHDAY=</code></td>
  <td>4</td>
  </tr>
  <tr>
  <td>8/1/2021</td>
  <td>11234</td>
  <td><code>DCNS=ACCEPTED\|HYPO=YES</code></td>
  <td>7</td>
  </tr>
  <tr>
  <td>8/1/2021</td>
  <td>11234</td>
  <td><code>DCNS=CONFIRMED\|HYPO=JULY 25th 2019</code></td>
  <td>10</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I have a query that is trying to stack certain data in a single row. The query is trying to pull back: the date, unique_id, and get the first and last HYPO.</p>
  <pre><code>SELECT
      DTE,
      UNIQUE_ID,
      MIN(CASE WHEN HYPO &gt; 0 THEN TRIM(SUBSTR(ROW_DATA, HYPO + 7, INSTR(ROW_DATA, '|', HYPO + 7) - (HYPO + 7))) END ) FIRST_HYPO,
      MAX(CASE WHEN HYPO &gt; 0 THEN TRIM(SUBSTR(ROW_DATA, HYPO + 7, INSTR(ROW_DATA, '|', HYPO + 7) - (HYPO + 7))) END ) LAST_HYPO
  FROM
      (SELECT DTE, UNIQUE_ID, ROW_DATA, INSTR(ROW_DATA, '| HYPO=') as HYPO
      FROM
          (SELECT TO_CHAR(DTE, 'MM/DD/YYYY' AS DTE, UNIQUE_ID, '| ' || ROW_DATA || ' |' as ROW_DATA, CALL_ROW_ID
          FROM DATATABLE))
  
  </code></pre>
  <p>What I am trying to get is :</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>DTE</th>
  <th>UNIQUE_ID</th>
  <th>FIRST_HYPO</th>
  <th>LAST_HYPO</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>8/1/2021</td>
  <td>11234</td>
  <td>NEW_CREDIT</td>
  <td>JULY 25th 2019</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>But what I am getting back is</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>DTE</th>
  <th>UNIQUE_ID</th>
  <th>FIRST_HYPO</th>
  <th>LAST_HYPO</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>8/1/2021</td>
  <td>11234</td>
  <td>GIVE ME BIRTHDAY</td>
  <td>YES</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Its odd because the data is in order yet the MIN/MAX is choosing different rows. I tried to do a <code>ORDER BY</code> at all levels of the query and it doesn't fix the result. It always pulls back the same fields.</p>
  <p>Does anyone know how to fix this?</p>