How to incrementally count the number of repeated instances

Question

I want to incrementally count the number of repeated instances in a table, so if I have a table like this:

id   | name  | status |
-----------------------
1    | John  |    1   | 
2    | Jane  |    1   |
4    | John  |    1   |
5    | John  |    1   |
6    | Jane  |    1   |
7    | John  |    1   |

Using the column "name" as reference, the output would be

id   | name  | status | count |
------------------------------
1    | John  |    1   |   1   |
2    | Jane  |    1   |   1   |
4    | John  |    1   |   2   |
5    | John  |    1   |   3   |
6    | Jane  |    1   |   2   |
7    | John  |    1   |   4   |

The DBMS implementation is MySQL, version 5.6

Answer 1

In MySQL 5.x, where window functions are not available, one option uses a correlated subquery:

select t.*,
    (
        select count(*)
        from mytable t1
        where t1.name = t.name and t1.id <= t.id
    ) rn
from mytable t

You could also do this with user variables:

select t.*,
    case when @name = (@name := name)
        then @rn := @rn + 1
        else @rn := 1
    end as rn
from (select * from mytable order by name, id) t
cross join (select @name := null, @rn := 0) x

There are pros and cons to both approaches. The second solution scales better than the first against a large dataset, however user variables are tricky in MySQL, and are now officially planned for deprecation in a future version.