CODEWITHSUNDEEP
sqlsql-serverwindow-functions
user3603308
user3603308

Reputation: 355

Assign a unique id sql server

I have a table that has the following records:

PatientId    PersonIdentifier
1            abc
2            abc
3            def
4            def
5            def

I want to assign a personId that is numeric. So person identifier abc will both have Id 1 and def will all have Id 2. How do I do this?

I've tried row num, rank etc, but can't seem to get it to work.

Cheers

Upvotes: 0

Views: 42

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239774

DENSE_RANK would seem the correct option here.

select PatientId, PersonIdentifier,
DENSE_RANK() OVER (ORDER BY PersonIdentifier) as Id
from SomeUnknownTable

You want ranking because you want to assign multiple rows the same values (and this isn't a PARTITIONing situation). And you want dense ranking so that multiple rows assigned the same value don't "consume" additional values behind the scenes.

Upvotes: 2

Related Questions