title: SQL query for run-length, or consecutive identical value encoding
body: <p>My goal is to take a set of data that is ordered by <code>id</code> and return a resultset that indicates the number of consecutive rows where the <code>val</code> column is identical. E.g. given this data:</p>
  
  <pre><code>| id | val |
  |  1 |  33 |
  |  2 |  33 |
  |  3 |  44 |
  |  4 |  28 |
  |  5 |  44 |
  |  6 |  44 |
  </code></pre>
  
  <p>I would like to see this result:</p>
  
  <pre><code>| id | val | run_length |
  | 1  | 33  | 2          |
  | 3  | 44  | 1          |
  | 4  | 28  | 1          |
  | 5  | 44  | 2          |
  </code></pre>
  
  <p>The id column in the resultset is optional. In fact, if it makes it significantly harder, then just leave that column out of the result. I sort of like having it because it "pins" the resultset to a particular location in the table. </p>
  
  <p>I am primarily interested in the result in free database engines. My order of preference for a solution is:</p>
  
  <ol>
  <li>SQLite </li>
  <li>Postgres  </li>
  <li>MySQL  </li>
  <li>Oracle  </li>
  <li>SQL Server  </li>
  <li>Sybase</li>
  </ol>