title: How to make ROW_NUMBER work with duplicate records?
body: <p>I have a table with transactional records that are duplicates. These are not bad duplicates, but they need to be assigned values to make them unique to the table. Being the use of the records is to look at them in order over time, I am using ROW_NUMBER to partition them by ID and ORDER BY Time.</p>
  
  <p>Sometimes it works. Sometimes it fails.</p>
  
  <p><strong>Example</strong></p>
  
  <pre><code>CASE WHEN [Record] = 'Start' 
       THEN 0 
  ELSE -1 + ROW_NUMBER() OVER(PARTITION BY [ID] ORDER BY [Date] DESC
  END AS [RowNo]
  </code></pre>
  
  <p><strong>Expected Results</strong></p>
  
  <pre><code>ID,   Date,  RowNo
  2002, 12:30, 1
  2002, 12:29, 2
  2002, 12:29, 3
  2002, 12:29, 4
  2002, 12:28, 5
  3212, 01:10, 1
  3212, 01:09, 2
  3212, 01:08, 3
  </code></pre>
  
  <p><strong>Actual Results</strong></p>
  
  <pre><code>ID,   Date,  RowNo
  2002, 12:30, 1
  2002, 12:29, 2 &lt;---Right
  2002, 12:29, 2 &lt;---Wrong
  2002, 12:29, 3 &lt;---Right
  2002, 12:28, 4
  3212, 01:10, 1
  3212, 01:09, 2
  3212, 01:08, 3
  </code></pre>
  
  <p>Is there a better approach to ensure accuracy?</p>