SQL Server : Identity column by group

Question

How can I add an identity number so that when a row is inserted an incremental number is assigned as below by a trigger? I am using SQL Server.

1    AAA  
2    AAA  
3    BBB  
4    CCC  
5    CCC  
6    CCC  
7    DDD  
8    DDD  
9    EEE  
....

And I want to convert it to:

1    AAA   1
2    AAA   2
4    CCC   1
5    CCC   2
6    CCC   3
7    DDD   1
8    DDD   2

Answer 1

You could create a FUNCTION which get a name and gives MAX identity for given parameter:

CREATE FUNCTION [dbo].[GetIdentityForName] (@Name VARCHAR(MAX))
RETURNS INT
AS
  BEGIN
      RETURN
        (SELECT ISNULL(MAX(NameId),0)+1
         FROM  YourTable
         WHERE Name = @Name);
  END  

and then set DefaultValue for NameId for call the function when a record has been inserted like this:

ALTER TABLE YourTable ADD CONSTRAINT
    DF_Identity_NameId DEFAULT ([dbo].[GetIdentityForName](Name)) FOR NameId

Assuming that YourTable is (Id, Name, NameId).

I hope to be helpful for you :)

Answer 2

There is no reason why you have to store the value. You could calculate it when you need it:

select t.*, row_number() over (partition by name order by id) as nameId
from t;