SQL Increment count on repeat values?

Question

Not sure how to ask this question, I'm not looking for the total count of a value in a column, rather I want to incrementally count repeat values. For example:

If my table looks like this:

1, ken
2, ken
3, adam
4, ken
5, adam
6, dan

I want to add a column during my select that tags duplicates with an incremental number like this:

1, ken, 1
2, ken, 2
3, adam, 1
4, ken, 3
5, adam, 2
6, dan, 1

Answer 1

You can do this via ROW_NUMBER() with a PARTITION on your second column, ordering by the first:

Select  Col1, 
        Col2, 
        Row_Number() Over (Partition By Col2 Order By Col1 Asc) As Col3
From    YourTable