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

what database are you using? – Dalorzo Sep 02 '17 at 04:03 — Dalorzo, Sep 02 '17 at 04:03
Do you also want to filter out the rows with id 3 and 9? – Carsten Massmann Sep 02 '17 at 07:08 — Carsten Massmann, Sep 02 '17 at 07:08

score 3 · Accepted Answer · answered Sep 02 '17 at 04:18

3

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 :)

answered Sep 02 '17 at 04:18

Ali Adlavaran

3,697
2
23
47

is it possible to get the answer in same table ? – Mansoor Sep 04 '17 at 05:44
You means get the results as `Query` without changing table definition? – Ali Adlavaran Sep 04 '17 at 06:53
i am getting this error when i put it into same table Error Message: Maximum stored procedure , function , trigger or view nesting level exceeded (limit 32) – Mansoor Sep 04 '17 at 07:17

score 3 · Answer 2 · answered Sep 02 '17 at 06:35

3

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;

answered Sep 02 '17 at 06:35

Gordon Linoff

1,242,037
58
646
786

In this solution, if a row is deleted, the row_numbers of the remaining rows change. That might be a reason to store the value. – Rik D Nov 18 '20 at 09:44

SQL Server : Identity column by group

2 Answers2