title: return the first row of value when a column has the same value 4 times consecutively
body: <p>Thanks for the help in advance. I'm working in MS SQL Server 2008 and trying to capture data from the first row with a value of "0" in the Auto_Manual column, where there are at least four "0"s in a row. Here is a sample of my data:</p>
  
  <pre><code>Lot      DateTimeStamp  Auto_Manual rn
  ----------------------------------------
  abc123   1/4/2017 2:08      1        1
  abc123   1/4/2017 2:10      1        2
  abc123   1/4/2017 2:12      1        3
  abc123   1/4/2017 2:14      1        4
  abc123   1/4/2017 2:16      1        5
  abc123   1/4/2017 2:18      1        6
  abc123   1/4/2017 2:20      1        7
  abc123   1/4/2017 2:22      1        8
  abc123   1/4/2017 2:24      0        1
  abc123   1/4/2017 2:26      1        9
  abc123   1/4/2017 2:28      1        10
  abc123   1/4/2017 2:30      1        11
  abc123   1/4/2017 2:32      0        2
  abc123   1/4/2017 2:34      1        12
  abc123   1/4/2017 2:36      1        13
  abc123   1/4/2017 2:38      0        3
  abc123   1/4/2017 2:40      0        4
  abc123   1/4/2017 2:42      0        5
  abc123   1/4/2017 2:44      0        6
  abc123   1/4/2017 2:46      0        7 
  </code></pre>
  
  <p>The result I'm looking for is:</p>
  
  <pre><code>Lot      DateTimeStamp  Auto_Manual rn
  ----------------------------------------
  abc123   1/4/2017 2:38      0        3
  </code></pre>
  
  <p>So far, all I've been able to do is get the row number partitioned by the Lot and Auto_Manual columns:</p>
  
  <pre><code>select 
     Lot
    ,DateTimeStamp
    ,Auto_Manual
    ,ROW_NUMBER() over (partition by Lot, Auto_Manual 
    order by DateTimeStamp) rn
  
    from dbo.Table )  tmp
  
    order by Lot, DateTimeStamp
  </code></pre>
  
  <p>I'm able to select the row where rn = 1 for either Auto_Manual = 0 or 1, but that's not exactly what I'm looking to do. I only want the first row where there are 4 consecutive "0"s in the Auto_Manual column. Please note that an alternative could be to find the first Auto_Manual value of "0" where 8 minutes later the value is still "0". This only works if there are only "0" in between as well...</p>
  
  <p>Any help would be greatly appreciated.</p>
  
  <p>Thanks</p>