title: SQL: age of oldest items
body: <p>I have following Oracle database. A certain ID can have an accepted status, but can continue in a failed status, and return back in an accepted status. This can happen X time, so it can be called a loop</p>
  
  <pre><code>ID       STATUS         TIME INSERT
  ------------------------------
  1        Accepted       01:00:00
  1        Failed         02:00:00
  1        Accepted       02:30:00
  2        Accepted       02:33:00
  </code></pre>
  
  <p>I want to calculate the age of the oldest item with an accepted status.
  This means the result of SYSDATE - TIME INSERT</p>
  
  <blockquote>
    <p>As Result of this data I expect the 3d record time value, 02:30:00</p>
  </blockquote>
  
  <p>I need to check for the youngest Time of every ID and than the oldest lifetime of all the youngest lifetimes of the ID's.
  This looks quite complicated for me.</p>
  
  <p>The workflow steps are:</p>
  
  <ol>
  <li>the minimum time value of a specific id in the state Accepted</li>
  <li>Step 1 for every id in the accepted state</li>
  <li>the max value of all the min values I calculated in step 2.</li>
  </ol>
  
  <p>At this moment I have following code</p>
  
  <pre><code>select min(TO_CHAR(TO_DATE('20000101','yyyymmdd')+(SYSDATE - dm1.time_insert),'hh24:mi:ss'))
  from db dm1 
  where dm1.status='accepted'
  group by dm1.id;
  </code></pre>
  
  <p>This gives me a sequence of the min values of all records. But now I need the max value from all this min values, how do I do that?
  Anyone who can solve this case?</p>