MySQL 5.7

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

CREATE TABLE tab(ID INT, ctr VARCHAR(1))
INSERT INTO tab VALUES(1,'A'),(2,'A')
SET @ctr = 'A';

INSERT INTO tab 
SELECT COALESCE(MAX(ID)+1,1),@ctr FROM tab WHERE ctr = @ctr
SELECT * FROM tab


ID
ctr


1
A

2
A

3
A


SET @ctr = 'B';

INSERT INTO tab 
SELECT COALESCE(MAX(ID)+1,1),@ctr FROM tab WHERE ctr = @ctr
SELECT * FROM tab


ID
ctr


1
A

2
A

3
A

1
B


SET @ctr = 'B';

INSERT INTO tab 
SELECT COALESCE(MAX(ID)+1,1),@ctr FROM tab WHERE ctr = @ctr
SELECT * FROM tab


ID
ctr


1
A

2
A

3
A

1
B

2
B