I trying to assign a unique # to each row of a table starting at 1 for each new key value. For example, it table contained the following data about cities and states and the key is State:

State City
----- ----
Connecticut Hartford
Connecticut Newtown
Connecticut Stafford
Connecticut New Britain
Rhode Island Charlestown
Rhode Island Westerly

I'd be looking for the following results:

State City Counter
----- ---- -------
Connecticut Hartford 1
Connecticut Newtown 2
Connecticut Stafford 3
Connecticut New Britain 4
Rhode Island Charlestown 1
Rhode Island Westerly 2

Any hints???