CODEWITHSUNDEEP
sqlsql-server
Kathius
Kathius

Reputation: 15

SQL unique auto-increment column in the context of another column

I would like to create a column in the existing table with an auto-incrementing key based on Prefix column, with the output expected as follows:

Prefix Id
123 1
123 1
444 2
111 3
999 4
123 1
444 2

Do you know any simple solution how to do that? I found out a solution for mysql but it is not working. I am using sql management studio with sql server

I tried solutions from this question but it does not work. I have errors

Upvotes: 1

Views: 793

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can use dense_rank():

select t.*, dense_rank() over (order by prefix) as id
from t;

In SQL Server, you can phrase this as an updatable CTE:

with toupdate as (
      select t.*, dense_rank() over (order by prefix) as new_id
      from t
     )
update toupdate
    set id = new_id;

Upvotes: 2

Related Questions