title: Oracle Analytic functions - resetting a windowing clause
body: <p>I have the following data set.</p>
  
  <pre><code>create table t1 (
    dept number,
    date1 date
  );
  
  Table created.
  
  insert into t1 values (100, '01-jan-2013');
  insert into t1 values (100, '02-jan-2013');
  insert into t1 values (200, '03-jan-2013');
  insert into t1 values (100, '04-jan-2013');
  commit;
  </code></pre>
  
  <p>MY goal is to create a rank column that resets each time the department changes. The closest column that I can use for "partition by" clause is dept, but that won't give me the desired result.</p>
  
  <pre><code>SQL&gt; select * from t1;
  
        DEPT DATE1
  ---------- ---------
         100 01-JAN-13
         100 02-JAN-13
         200 03-JAN-13
         100 04-JAN-13
  
  select dept,  
         date1,
         rank () Over (partition by dept order by date1) rnk
  from t1
  order by date1;
  
        DEPT DATE1            RNK
  ---------- --------- ----------
         100 01-JAN-13          1
         100 02-JAN-13          2
         200 03-JAN-13          1
         100 04-JAN-13          3
  </code></pre>
  
  <p>The desired output is as follows. The last rnk=1 is becuase the Jan-04 record is the first record after the change.</p>
  
  <pre><code>      DEPT DATE1            RNK
  ---------- --------- ----------
         100 01-JAN-13          1
         100 02-JAN-13          2
         200 03-JAN-13          1
         100 04-JAN-13          1  &lt;&lt;&lt;----------
  </code></pre>
  
  <p>Any pointers?</p>