title: Updating column on comparing of two rows value from same table
body: <p>I am trying to format my result into the following:</p>
  
  <pre><code>ID    pred  probability cost  
  101   1     0.15        0.9  
  101   2     0.85        0.1  
  102   2     0.25        0.55  
  102   1     0.75        0.44 
  </code></pre>
  
  <p>The required format is to get result column on the basis of higher probability for e.g., for <code>ID</code> 101 the probability of <code>pred</code> value '2' is higher so result column will have '2' as value for <code>ID</code> '101' :</p>
  
  <pre><code>ID    result  pred  cost  
  101   2       1     0.9  
  101   2       2     0.1  
  102   1       2     0.55  
  102   1       1     0.44  
  </code></pre>
  
  <p>I tried getting the max <code>probability</code> by grouping on basis of <code>ID</code> like:  </p>
  
  <pre><code>SELECT  
     ID,  
      MAX(probability) prob  
  FROM  
      table  
  GROUP BY  
      ID  
  </code></pre>
  
  <p>and concatenating the other rows on basis of <code>ID</code> and <code>probability</code> like:  </p>
  
  <pre><code>with temp as (  
  SELECT  
          ID,  
          MAX(probability) prob  
      FROM  
          table  
      GROUP BY  
          ID  
  )  
  select id,
  base.ID, base.pred, base.cost
  from base
  where temp.ID = base.ID and base.probability = temp.prob
  </code></pre>
  
  <p>but not getting the expected output. Any suggestions?</p>