Posted by David Portas on 03/19/07 19:49

On 19 Mar, 18:26, "jrpfinch" <jrpfi...@gmail.com> wrote:
> 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?

To ensure you get a good answer to a question like this always state
what version you are using and include a CREATE TABLE statement that
defines the key(s) in your table.

Here's my guess based on the assumption that (Id, Cat) is unique. Note
that if CatID is always to be based on the values of the other two
columns then it might make more sense NOT to include it in the table.
Instead compute it whenever the results are displayed.

UPDATE tbl
SET CatID =
(SELECT COUNT(*)
FROM tbl AS t
WHERE Cat = tbl.Cat
AND ID <= tbl.ID);

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]