assign a consecutive ID for rows with specified value-CodePudding

How to assign a consecutive ID for rows with specified value in my case Identifier and ideally in ascending order by Value?

The table should look like this:

Identifier      ID  Name        Value
identifier1     1   example1    value100
identifier1     2   example2    value200
identifier1     3   example3    value400
identifier2     1   example3    value100
identifier2     2   example5    value500
identifier3     1   example11   value600
identifier3     2   example12   value800

My code so far only gives a consecutive ID for the whole Table #Temp:

CREATE TABLE #Temp (
        Identifier          NVARCHAR(256)
    ,   ID  INT NOT NULL    IDENTITY( 1, 1)
    ,   Name                NVARCHAR(256)
    ,   Value               NVARCHAR(256)
    )

WHILE Condition
BEGIN


SELECT @Identifier  = Identifier    (..)
SELECT @Table       = Table         (..)

SET @Sql = N'
        SELECT  
        '''   @Identifier   '''
    ,   Name
    ,   MAX(Value)
        FROM '   @Table  ' WITH (NOLOCK)
        GROUP BY Name
        ORDER BY Name
        '

    INSERT INTO #Temp
    EXEC ( @Sql )
END

CodePudding user response：

Looks like you are trying to assign consecutive id’s to each group of rows with the same identifier. All you need is ROW_NUMBER()

Adding ID’s with ROW_NUMBER()

SELECT ID = ROW_NUMBER() OVER (PARTITION BY Identifier ORDER BY [Value]),*
From YourTable