Apply nth occurrence number on each distinct value in a column

I have a table with a column of values where each value occurs a variable number of times (i.e., one value may occur 1 time, and another value may occur 3 times). I need to add a column that identifies the occurrence sequence # of its corresponding value.

Input Table

SOURCE_VAL

a

a

b

c

c

c

Output table

SEQUENCE_VAL
SOURCE_VAL

1
a

2
a

1
b

1
c

2
c

3
c

What would the SQL for this be?

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

StackExchange Database Administrators Q#298707, answer score: 3