Apply nth occurrence number on each distinct value in a column

Question

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?

Answer 1

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

Answer 2

Since you are using MySQL 5.5, I have some interesting news for you. Did you know that the MyISAM storage engine supports an auto increment primary key with a second column ? I wrote about this Apr 21, 2012 : How can you have two auto-incremental columns in one table? (See the MySQL Docs on this Auto Increment feature unique to MyISAM).

NOTE : In all honesty, no one should be using MyISAM any more. Since you are using MySQL 5.5, I would dare to make this suggestion. I actually have written examples in that 9.5 year old post.

In reality, you should be using MySQL 5.7 or 8.0 and stay away from MyiSAM. In that instance, you would using @nbk's answer against InnoDB Tables.