Cumulative Counting in SQL

I am struggling to write a query that gets the cumulative distinct counts for values in a column.

table column:           desired column:
a                       1
b                       1
c                       1
c                       2
c                       3
a                       2
a                       3

Explanation:

row 1 has value a, there are no preceding rows with value a so we count a once and get 1.

row 2 has value b, there are no preceding rows with value b so we count b once and get 1.

row 3 has value c, there are no preceding rows with value c so we count c once and get 1.

row 4 has value c, there is 1 preceding row with value c so we count c twice and get 2.

row 5 has value c, there are 2 preceding rows with value c so we count c thrice and get 3.

row 6 has value a, there is 1 preceding row with value a so we count a twice and get 2.

row 7 has value a, there are 2 preceding rows with value a so we count a thrice and get 3.

Any help would be great!

If your version of SQL supports it, then ROW_NUMBER is one option here:

SELECT
    col,
    ROW_NUMBER() OVER (PARTITION BY col ORDER BY col) cn
FROM yourTable;

Demo

Note that the ORDER BY clause used above in ROW_NUMBER is arbitrary, since within a partition of column values, all would have the same value.