MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

SELECT group_id ,
	   rn
FROM ( SELECT group_id,
              @rn := IF(@prev = group_id, @rn + 1, 1) AS rn,
              @prev := group_id
       FROM (   SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 222 AS `group_id`
                UNION ALL 
                SELECT 222 AS `group_id`
                UNION ALL 
                SELECT 222 AS `group_id`
                UNION ALL 
                SELECT 222 AS `group_id`
                UNION ALL 
                SELECT 333 AS `group_id`
                UNION ALL 
                SELECT 333 AS `group_id`
				) `m`
       JOIN (SELECT @prev := NULL, @rn := 0) AS vars
       ORDER BY group_id
     ) AS T1

group_id	rn
111	1
111	2
111	3
111	4
111	5
111	6
111	7
111	8
111	9
222	1
222	2
222	3
222	4
333	1
333	2

group_id	rn
111	1
111	2
111	3
111	4
111	5
111	6
111	7
111	8
111	9
222	1
222	2
222	3
222	4
333	1
333	2

group_id	rn
111	1
111	2
111	3
111	4
111	5
111	6
111	7
111	8
111	9
222	1
222	2
222	3
222	4
333	1
333	2

group_id	rn
111	1
111	2
111	3
111	4
111	5
111	6
111	7
111	8
111	9
222	1
222	2
222	3
222	4
333	1
333	2