MySQL 8.4

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

CREATE TABLE test ( some_group varchar(10), some_value int );
INSERT INTO test (some_group, some_value)
VALUES  ('apple', 4)
    ,   ('apple', 5)
    ,   ('apple', 5)
    ,   ('apple', 5)
    ,   ('apple', 6)
    ,   ('apple', 6)
    ,   ('banana', 12)
    ,   ('banana', 12)
    ;

Records: 8  Duplicates: 0  Warnings: 0


SELECT some_group
    ,  some_value
    ,  COUNT(*) OVER (PARTITION BY some_group
                          ORDER BY some_value DESC
                         ) AS row_num
FROM test;


some_group
some_value
row_num


apple
6
2

apple
6
2

apple
5
5

apple
5
5

apple
5
5

apple
4
6

banana
12
2

banana
12
2


SELECT some_group
    ,  some_value
    ,  COUNT(*) OVER (PARTITION BY some_group
                          ORDER BY some_value DESC
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                         ) AS row_num
FROM test;


some_group
some_value
row_num


apple
6
1

apple
6
2

apple
5
3

apple
5
4

apple
5
5

apple
4
6

banana
12
1

banana
12
2

some_group	some_value	row_num
apple	6	2
apple	6	2
apple	5	5
apple	5	5
apple	5	5
apple	4	6
banana	12	2
banana	12	2