title: select max from previous group
body: <p>I am trying to get the MAX value from the previous group. Here is a sample table.</p>
  
  <pre><code>group_id number,
  cycle number,
  time number);
  
  insert into cycle_times (group_id, cycle, time) values (1, 1, 1);
  insert into cycle_times (group_id, cycle, time) values (1, 1, 2);
  insert into cycle_times (group_id, cycle, time) values (1, 1, 3);
  insert into cycle_times (group_id, cycle, time) values (1, 1, 4);
  insert into cycle_times (group_id, cycle, time) values (1, 2, 5);
  insert into cycle_times (group_id, cycle, time) values (1, 2, 6);
  insert into cycle_times (group_id, cycle, time) values (1, 2, 7);
  insert into cycle_times (group_id, cycle, time) values (1, 2, 8);
  insert into cycle_times (group_id, cycle, time) values (1, 3, 9);
  insert into cycle_times (group_id, cycle, time) values (1, 3, 10);
  insert into cycle_times (group_id, cycle, time) values (1, 3, 11);
  insert into cycle_times (group_id, cycle, time) values (1, 3, 12);
  insert into cycle_times (group_id, cycle, time) values (2, 1, 1);
  insert into cycle_times (group_id, cycle, time) values (2, 1, 2);
  insert into cycle_times (group_id, cycle, time) values (2, 1, 3);
  insert into cycle_times (group_id, cycle, time) values (2, 1, 4);
  insert into cycle_times (group_id, cycle, time) values (2, 2, 5);
  insert into cycle_times (group_id, cycle, time) values (2, 2, 6);
  insert into cycle_times (group_id, cycle, time) values (2, 2, 7);
  insert into cycle_times (group_id, cycle, time) values (2, 2, 8);
  insert into cycle_times (group_id, cycle, time) values (2, 3, 9);
  insert into cycle_times (group_id, cycle, time) values (2, 3, 10);
  insert into cycle_times (group_id, cycle, time) values (2, 3, 11);
  insert into cycle_times (group_id, cycle, time) values (2, 3, 12);
  </code></pre>
  
  <p>This query works for the first group (just by virtue of it being first, I think). Each group/cycle has the max time from the previous group/cycle. But I want it to start over with each group_id so that group 2 cycle 1 prev_max = 0 and group 2 cycle 2 prev_max = 4 and so on.</p>
  
  <pre><code>select group_id, cycle, max_time, lag(max_time, 1, 0) over (order by group_id, cycle) prev_max 
  from (
  select distinct group_id, cycle, max(time) over (partition by group_id, cycle) max_time
  from cycle_times
  order by group_id, cycle
  )order by group_id, cycle;
  
  GROUP_ID    CYCLE   MAX_TIME    PREV_MAX
  ==========================================
  1           1       4           0
  1           2       8           4
  1           3       12          8
  2           1       4           12
  2           2       8           4
  2           3       12          8
  </code></pre>