mysql 5.5 – Apply nth occurrence number on each distinct value in a column

In mysql 5.5
you can use user defined variables for that

CREATE TABLE tab1
    (`SOURCE_VAL` varchar(1))
;
    
INSERT INTO tab1
    (`SOURCE_VAL`)
VALUES
    ('a'),
    ('a'),
    ('b'),
    ('c'),
    ('c'),
    ('c')
;
SELECT IF(@s_val = SOURCE_VAL,@rn := @rn +1 , @rn := 1) As SEQUENCE_VAL, @s_val := SOURCE_VAL   as SOURCE_VAL FROM  tab1,(SELECT @rn := 0, @s_val := '') t1 ORDER BY SOURCE_VAL
SEQUENCE_VAL | SOURCE_VAL
-----------: | :---------
           1 | a         
           2 | a         
           1 | b         
           1 | c         
           2 | c         
           3 | c         

db<>fiddle here

Snow flake as row_number for that

SELECT
 ROW_NUMBER()
  OVER (PARTITION BY SOURCE_VAL ORDER BY SOURCE_VAL DESC) SEQUENCE_VAL 
SOURCE_VAL
FROM  tab1