title: Resetting Row number according to record data change
body: <p>I have got the set of data as follow</p>
  
  <pre><code>name  date  
  x     2014-01-01
  x     2014-01-02
  y     2014-01-03
  x     2014-01-04
  </code></pre>
  
  <p>and I'm trying to get this result</p>
  
  <pre><code>name  date           row_num
  x     2014-01-01      1
  x     2014-01-02      2
  y     2014-01-03      1
  x     2014-01-04      1
  </code></pre>
  
  <p>I have tried to run this query</p>
  
  <pre><code>select name,
      date,
      row_number () over (partition by name order by date) as row_num
  from myTBL
  </code></pre>
  
  <p>but unfortunately I get this result</p>
  
  <pre><code>name  date           row_num
  x     2014-01-01      1
  x     2014-01-02      2
  y     2014-01-03      1
  x     2014-01-04      3
  </code></pre>
  
  <p>Please help.</p>