title: T-SQL Remove "duplicate/non-interesting" data rows
body: <p>I have the following set of data (sample given)</p>
  
  <pre><code>ID         Status  Code   Type       ModDate
  1234       1       1      AB         1995-04-01
  1234       1       1      CD         1998-08-31
  1234       1       1      AB         2003-08-31
  1234       1       NULL   AB         2008-11-08
  1234       1       2      AB         2013-11-09
  1234       1       1      EF         2013-11-18
  ...
  </code></pre>
  
  <p>As these data has to be viewed on some sort of timeline, I want to read just the following from the database, as only the <code>Type</code> changes are of interest:</p>
  
  <pre><code>ID         Status  Code   Type       ModDate
  1234       1       1      AB         1995-04-01
  1234       1       1      CD         1998-08-31
  1234       1       1      AB         2003-08-31
  1234       1       1      EF         2013-11-18
  ...
  </code></pre>
  
  <p>How can this be done? I tried to partition the data and give some row numbers, but it gives me headaches becuase the <code>Type</code> is grouped.</p>
  
  <pre><code>SELECT
      ID, Status, Code, Type, ModDate,
      MIN(ModDate) OVER (PARTITION BY ID, Type) MinModDate,
      MAX(ModDate) OVER (PARTITION BY ID, Type) MaxModDate,
      ROW_NUMBER() OVER (PARTITION BY ID, Type ORDER BY ModDate) RowNumber
  FROM Data
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>ID         Status  Code   Type     ModDate       MinModDate    MaxModDate   RowNumber
  1234       1       1      AB       1995-04-01    1995-04-01    2013-11-09   1
  1234       1       1      CD       1998-08-31    1998-08-31    1998-08-31   1
  1234       1       1      AB       2003-08-31    1995-04-01    2013-11-09   2
  1234       1       NULL   AB       2008-11-08    1995-04-01    2013-11-09   3
  1234       1       2      AB       2013-11-09    1995-04-01    2013-11-09   4
  1234       1       1      EF       2013-11-18    2013-11-18    2013-11-18   1
  ...
  </code></pre>
  
  <p>Output expected:</p>
  
  <pre><code>ID         Status  Code   Type     ModDate       MinModDate    MaxModDate   RowNumber
  1234       1       1      AB       1995-04-01    1995-04-01    2013-11-09   1
  1234       1       1      CD       1998-08-31    1998-08-31    1998-08-31   1
  1234       1       1      AB       2003-08-31    1995-04-01    2013-11-09   1
  1234       1       NULL   AB       2008-11-08    1995-04-01    2013-11-09   2
  1234       1       2      AB       2013-11-09    1995-04-01    2013-11-09   3
  1234       1       1      EF       2013-11-18    2013-11-18    2013-11-18   1
  ...
  </code></pre>
  
  <p>Can this be achieved easily without using cursors?</p>