Sorry if this seems too easy to be interesting - I have yet to find an
elegant solution, as I am completely new at this so don't really know
what to look for. I have the following table:

Id Cat
1 A
2 B
3 C
4 A
5 B
6 B
7 C
....

I would like to create a new column, CatId, which has a value n, which
is the nth appearance of the record's category (ordered by Id). In
this case it would be 1,1,1,2,2,3,2 because e.g. Id=6 is the third
appearance of the letter B so it would equal 3.

How could I do this in T-SQL?