By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE carts
(`id` int, `material_code` varchar(6))
;
INSERT INTO carts
(`id`, `material_code`)
VALUES
(1, 'ABC100'),
(2, 'DEF200'),
(3, 'ABC100'),
(4, 'ABC100'),
(5, 'DEF200')
Records: 5 Duplicates: 0 Warnings: 0
SELECT id, material_code, item_number
FROM (SELECT id,
@i := (CASE WHEN @m = material_code THEN @i ELSE @i + 1 END) AS item_number,
@m := material_code AS material_code
FROM carts
JOIN (SELECT @m := '', @i := 0) v
ORDER BY material_code) c
ORDER BY id
| id | material_code | item_number |
|---|---|---|
| 1 | ABC100 | 1 |
| 2 | DEF200 | 2 |
| 3 | ABC100 | 1 |
| 4 | ABC100 | 1 |
| 5 | DEF200 | 2 |