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?

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

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/m...S,SQL.90).aspx
--