SQL add IDENTITY base on grouped column value

I have data like this:

FirstName     LastName
El            Even
Mike          Wheeler
Mike          Byers
Dustin        Henderson

And my desired output is to add Identity into each of the unique FirstName

ID   FirstName     LastName
1    El            Even
2    Mike          Wheeler
2    Mike          Byers
3    Dustin        Henderson 

The way I do this is:

/* part 1 */
SELECT IDENTITY(int, 1,1) AS ID, FirstName 
INTO TabTemp FROM TabName 
GROUP BY FirstName

/* part 2 */
SELECT B.ID, A.FirstName, A.LastName 
INTO TabTempFinal FROM TabName A, TabTemp B
WHERE A.FistName = B.FirstName

My question is can I achieve the result without using the part 2?

3 answers

  • answered 2017-12-06 01:34 Gordon Linoff

    Not really. You can't add an identity column to a table where the identity is not supposed to be unique. That is not how identity columns work.

    But, you can use dense_rank() instead:

    SELECT DENSE_RANK() OVER (ORDER BY FirstName) AS ID,
           FirstName, LastName
    INTO TabTempFinal
    FROM TabName ;
    

    This generates the unique indicator for each FirstName. The only difference is that the column is not an identity column.

  • answered 2017-12-06 01:34 Mureinik

    You could just use the dense_rank window function:

    SELECT DENSE_RANK() OVER (ORDER BY first_name) AS id,
           first_name,
           last_name
    FROM   tabname
    

  • answered 2017-12-06 01:36 Tim Biegeleisen

    You could do an update with a CTE using DENSE_RANK:

    WITH cte AS (
        SELECT FirstName, LastName, DENSE_RANK() OVER (ORDER BY FirstName) dr
        FROM TabName
    )
    
    UPDATE cte
    SET ID = dr;
    

    This assumes that you already have a column called ID.