Choosing the number of consecutive records

Question

I am trying to get the number of rows for each subsequence.

The initial table may 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 number of elements for each sequence of values:

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

So far, I have not been able to find a solution, at least not fast enough for large tables. Ideally, there would be an expression "group by" that would not spoil the order of the entries.

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