How do you shift values down in a column in an Oracle table?

Question

Given the following oracle database table:

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 

I want to shift the comment column one step down in relation to version, within its group, so that I get the following table:

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  

I have the following query:

              

    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;

That does most of this (still need a separate query to cover revision = 1), but it is very slow.

So my question is, how do I use Max here as efficiently as possible to pull out the highest revision for each group?

Answer 1

I would use lag not max

create table example_table(group_id number, revision number, comments varchar2(40));
insert into example_table values (1,1,1);
insert into example_table values (1,2,2);
insert into example_table values (1,3,null);
insert into example_table values (2,1,1);
insert into example_table values (2,2,2);
insert into example_table values (2,3,3);
insert into example_table values (2,4,null);

select * from example_table;

merge into example_table e
using (select group_id, revision, comments, lag(comments, 1) over (partition by group_id order by revision nulls last) comments1 from example_table) u
on (u.group_id = e.group_id and nvl(u.revision,0) = nvl(e.revision,0))
when matched then update set comments = u.comments1;

select * from example_table;