title: SQL query to return top X sequential descending rows, by group, for a particular value
body: <p>I need a query to return, by group, a true or false if the most recent x number of sequential rows, in descending date order, have a column with a false value where x can be different for each group.</p>
  
  <p>For example, a Configuration table would have the number of records that have to match sequentially by companyId and serviceId:</p>
  
  <pre>
  CompanyId     ServiceId      NumberOfMatchingSequentialRecords
  2             1              3
  3             2              2
  </pre>
  
  <p>The table to query against, say Logging, might have the following data:</p>
  
  <pre>
  CompanyId     ServiceId     SuccessfulConnect(bit)     CreateDate (desc order)
  2             1             0                          2009-12-09  9:54am
  2             1             0                          2009-12-09  9:45am
  2             1             0                          2009-12-09  9:36am
  2             1             1                          2009-12-08 10:16am
  2             1             1                          2009-12-07  3:24pm
  
  3             2             0                          2009-10-15 8:54am
  3             2             1                          2009-10-14 5:17pm
  3             2             0                          2009-10-13 4:32am
  3             2             1                          2009-10-13 1:19am
  </pre>
  
  <p>For the query to match, SuccessfulConnect must have 0/false values for the sequence by group (companyId, serviceId).</p>
  
  <p>The result of the query would then be...</p>
  
  <pre>
  CompanyId     ServiceId     Alert (bit)
  2             1             1
  3             2             0
  </pre>
  
  <p>...because companyId=2, serviceId=1 would return a true as the 3 most recent consecutive records in descending date order, as defined in the Configuration table, all had SuccessfulConnect as false.</p>
  
  <p>However, companyId=3 serviceId=2 would return a false because the 2 most recent consecutive records in descending date order, as defined in the Configuration table, did not both have false.</p>