title: RANK() OVER PARTITION with RANK resetting
body: <p>How can I get a RANK that restarts at partition change?
  I have this table:</p>
  
  <pre><code>ID    Date        Value  
  1     2015-01-01  1  
  2     2015-01-02  1 &lt;redundant  
  3     2015-01-03  2  
  4     2015-01-05  2 &lt;redundant  
  5     2015-01-06  1  
  6     2015-01-08  1 &lt;redundant  
  7     2015-01-09  1 &lt;redundant  
  8     2015-01-10  2  
  9     2015-01-11  3  
  10    2015-01-12  3 &lt;redundant  
  </code></pre>
  
  <p>and I'm trying to delete all the rows where the Value is not changed from the previous entry (marked with <strong>&lt; redundant</strong>).
  I've tried using cursors but it takes too long, as the table has ~50 million rows.</p>
  
  <p>I've also tried using RANK:</p>
  
  <pre><code>SELECT ID, Date, Value,
  RANK() over(partition by Value order by Date ASC) Rank,
  FROM DataLogging 
  ORDER BY Date ASC 
  </code></pre>
  
  <p>but I get:</p>
  
  <pre><code>ID    Date        Value  Rank   (Rank)
  1     2015-01-01  1      1      (1)
  2     2015-01-02  1      2      (2)
  3     2015-01-03  2      1      (1)
  4     2015-01-05  2      2      (2)
  5     2015-01-06  1      3      (1)
  6     2015-01-08  1      4      (2)
  7     2015-01-09  1      5      (3)
  8     2015-01-10  2      3      (1)
  9     2015-01-11  3      1      (1)
  10    2015-01-12  3      2      (2)
  </code></pre>
  
  <p>in parantheses is the Rank I would want, so that I can filter out rows with Rank = 1 and delete the rest of the rows. </p>
  
  <p>EDIT: I've accepted the answer that seemed the easiest to write, but unfortunately none of the answers runs fast enough for deleting the rows.
  In the end I've decided to use the <strong>CURSOR</strong> afterall. I've split the data in chuncks of about 250k rows and the cursor runs through and deletes the rows in ~11 mins per batch of 250k rows, and the answers below, with DELETE, take ~35 mins per batch of 250k rows.</p>