title: How do you shift values down in a column in an Oracle table?
body: <p>Given the following oracle database table:</p>
  
  <pre>
  group   revision    comment
  1       1           1               
  1       2           2 
  1       null        null 
  2       1           1               
  2       2           2 
  2       3           3 
  2       4           4 
  2       null        null 
  3       1           1               
  3       2           2 
  3       3           3 
  3       null        null 
  </pre>
  
  <p>I want to shift the comment column one step down in relation to version, within its group, so that I get the following table: </p>
  
  <pre>
  group   revision    comment
  1       1           null            
  1       2           1   
  1       null        2  
  2       1           null            
  2       2           1   
  2       3           2 
  2       4           3 
  2       null        4  
  3       1           null            
  3       2           1   
  3       3           2 
  3       null        3  
  </pre>
  
  <p>I have the following query:</p>
  
  <pre>              
  
      MERGE INTO example_table t1
      USING example_table t2
      ON (
         (t1.revision = t2.revision+1 OR 
         (t2.revision = (
            SELECT MAX(t3.revision) 
            FROM example_table t3 
            WHERE t3.group = t1.group
         ) AND t1.revision IS NULL)
      ) 
      AND t1.group = t2.group)
      WHEN MATCHED THEN UPDATE SET t1.comment = t2.comment;
  
  </pre>
  
  <p>That does most of this (still need a separate query to cover revision = 1), but it is very slow. </p>
  
  <p>So my question is, how do I use Max here as efficiently as possible to pull out the highest revision for each group? </p>