CODEWITHSUNDEEP
sqlsql-server
user2343837
user2343837

Reputation: 1015

Add key to unique values in the SQl database

My SQL data looks like this:

Col1
A
A
A
B
B
C
D

I want to add a key to only unique values. So the end result will look like this:

Col1  Col2
A       1
A       1
A       1
B       2
B       2
C       3
D       3

How can I do this?

Upvotes: 0

Views: 72

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can do this with the dense_rank() window function:

select col1, dense_rank() over (order by col1) as col2
from t;

This solves the problem as a query. If you want to actually change the table, then the code is more like:

alter table t add col2 int;

with toupdate as (
    select t.*, dense_rank() over (order by col1) as newcol2
    from t
)
update toupdate
    set col2 = newcol2;

Upvotes: 5

Related Questions