title: GROUP BY for continuous rows in SQL
body: <p>Given the following table:</p>
  <pre>
  <b>ID   State  Date</b>
  12   1      2009-07-16 10:00
  45   <b>2</b>      2009-07-16 13:00
  67   <b>2</b>      2009-07-16 14:40
  77   1      2009-07-16 15:00
  89   1      2009-07-16 15:30
  99   1      2009-07-16 16:00
  </pre>
  <p>Question:<br><b>How can i GROUP by the field &quot;State&quot;, while still maintaining the borders between the state changes?</b></p>
  <pre><code>SELECT MIN(ID) AS ID, State, MIN(Date) AS Date, COUNT(ID) AS Count
  FROM table GROUP BY State
  </code></pre>
  <p>results in the following:</p>
  <pre>
  <b>ID   State  Date              Count</b>
  12   1      2009-07-16 10:00  4
  45   2      2009-07-16 13:00  2
  </pre>
  <p><b>but this is the desired output:</b></p>
  <pre>
  <b>ID   State  Date              Count</b>
  12   1      2009-07-16 10:00  1
  45   2      2009-07-16 13:00  2
  77   1      2009-07-16 15:00  3
  </pre>
  <br>
  Is this possible in SQL? I didn't find a solution so far...