Selecting number of sequential records

Question

I'm trying to get number of rows per each subsequence.

The initial table could look like this:

+----+-------------+
| id | value       |
+----+-------------+
|  1 | a           |
|  2 | b           |
|  3 | b           |
|  4 | c           |
|  5 | a           |
|  6 | a           |
|  7 | a           |
|  8 | a           |
|  9 | c           |
|  10| c           |
+----+-------------+

The query should return the count of items for each sequence of values:

+----+-------------+
| value | count    |
+----+-------------+
|  a    | 1        |
|  b    | 2        |
|  c    | 1        |
|  a    | 4        |
|  c    | 2        |
+-------+----------+

So far I haven't been able to come up with a solution, at least not fast enough for large tables. Ideally there would be a "group by" statement that doesn't mess up the order of records.

Answer 1

SELECT value, count(*) FROM (
  SELECT value,
    (CASE WHEN @v != value THEN @i:=@i+1 ELSE @i END) gid,
     @v := value FROM myTable, (SELECT @v:='', @i := 0) vars
) tbl
GROUP BY gid