sql - Oracle Analytic functions - resetting a windowing clause -

i have following data set.

create table t1 (   dept number,   date1 date );  table created.  insert t1 values (100, '01-jan-2013'); insert t1 values (100, '02-jan-2013'); insert t1 values (200, '03-jan-2013'); insert t1 values (100, '04-jan-2013'); commit; 

my goal create rank column resets each time department changes. closest column can use "partition by" clause dept, won't give me desired result.

sql> select * t1;        dept date1 ---------- ---------        100 01-jan-13        100 02-jan-13        200 03-jan-13        100 04-jan-13  select dept,          date1,        rank () on (partition dept order date1) rnk t1 order date1;        dept date1            rnk ---------- --------- ----------        100 01-jan-13          1        100 02-jan-13          2        200 03-jan-13          1        100 04-jan-13          3 

the desired output follows. last rnk=1 becuase jan-04 record first record after change.

      dept date1            rnk ---------- --------- ----------        100 01-jan-13          1        100 02-jan-13          2        200 03-jan-13          1        100 04-jan-13          1  <<<---------- 

any pointers?

this little complicated. instead of using rank() or like, use lag() see when changes. cumulative sum of flag.

select dept, date1,        case when startflag = 0 1             else 1+startflag+nvl(lag(startflag) on (order date1),0)        end rnk (select t1.*,              (case when dept = lag(dept) on (order date1)                    1                    else 0               end) startflag       t1      ) t1 order date1; 

here sqlfiddle.

edit:

this gordon editing own answer. oops. original query 90% of way there. identified groups numbers should increase, did not assign numbers within groups. level of row_number() in:

select dept, date1,        row_number() on (partition dept, grp order date1) rnk (select dept, date1, startflag,              sum(startflag) on (partition dept order date1) grp       (select t1.*,                    (case when dept = lag(dept) on (order date1)                          0                          else 1                     end) startflag             t1            ) t1      ) t1 order date1; 

so, overall idea following. first use lag() determine group begins (that is, there department change 1 date next). then, assign "group id" these, doing cumulative sum. these records enumerated. final step enumerate them using row_number().