title: oracle sql Find max avg of three attempts
body: <p>Hey need help with finding maximum average weight of three columnes (attempt1, attempt2 and attempt3 and showing which exercise they belong to and what date they occurred. </p>
  
  <p>Atm, the results of the below query outputs, all exercises, all dates they occurred on and all the avg weights. I want it to select the max avg weight, the exercise it belongs to and date it occurred. Attached is the result of the query and ive highlighted what I want to be the output. 
  <a href="https://i.stack.imgur.com/T0BkV.png" rel="nofollow noreferrer">Result of query</a></p>
  
  <pre><code> SELECT e.exercise_description
   , occ.occ_date, (COALESCE(OE.ATTEMPT1, 0)
   + COALESCE(OE.ATTEMPT2, 0)
    + COALESCE(OE.ATTEMPT3, 0))
     /(3 -(COALESCE(OE.ATTEMPT1 - OE.ATTEMPT1, 1)
      + COALESCE(OE.ATTEMPT2 - OE.ATTEMPT2, 1)
       + COALESCE(OE.ATTEMPT3 - OE.ATTEMPT3, 1))
    ) AS row_avg
     FROM EXERCISE E INNER JOIN Occurrence_Exercise OE
    ON E.EXERCISEID=OE.EXERCISEID
      INNER JOIN OCCURRENCE OCC ON OE.OCCURRENCEID=OCC.OCCURRENCEID;
  </code></pre>