t, MySQL - rextester

Language: Layout:

DROP TABLE IF EXISTS Table1;

CREATE TABLE Table1
    (`ID` int, `NAME` varchar(5))
;
    
INSERT INTO Table1
    (`ID`, `NAME`)
VALUES
    (1, 'Apple'),
    (2, 'Apple'),
    (3, 'Apple'),
    (4, 'Bravo'),
    (5, 'Bravo'),
    (6, 'Cat'),
    (7, 'Cat'),
    (8, 'Cat'),
    (9, 'Cat'),
    (10, 'Apple'),
    (11, 'Apple')
;

SELECT `ID`, `NAME`, rn,
        mark <= 2 as mark       
FROM (
        SELECT `ID`, `NAME`, rn, 
               @mark := IF(@grp = rn,
                           @mark + 1,
                           IF( @grp := rn, 1, 1)                                      
                           ) as  mark
        FROM (       
                SELECT `ID`, `NAME`, 
                       @rn := IF(@name = `NAME`,
                                 @rn,
                                 IF(@name := `NAME`, @rn + 1, @rn + 1)
                                ) as rn
                FROM Table1
                CROSS JOIN (SELECT @rn := 0, @name :='') var
                ORDER BY `ID`
             ) T
        CROSS JOIN (SELECT @mark := 0, @grp := 0 ) var
        ORDER BY rn, `ID` DESC
    ) Y
ORDER BY ID


absolute service time: 0,45 sec