Posted by Hugo Kornelis on 03/19/07 19:43

On 19 Mar 2007 11:26:36 -0700, jrpfinch 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?

Hi jrpfinch,

SELECT a.Id, a.Cat,
COUNT(*) AS CatId
FROM YourTable AS a
INNER JOIN YourTable AS b
ON b.Cat = a.Cat
AND b.Id <= a.Id
GROUP BY a.Id, a.Cat;

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Back to original message]