CODEWITHSUNDEEP
sqlsql-serverdatabase
star_kid
star_kid

Reputation: 191

Give unique IDs to Distinct names sql server

I have a sql server table containing names:

Serial  Name
1      Roger
2      John
3      John
4      Mike
5      Roger
6      John
7      Mike

I want to insert a UID column that will have unique IDs for unique names. So, it should look like,

Serial  Name    UID
1      Roger     1
2      John      2
3      John      2
4      Mike      3
5      Roger     1
6      John      2
7      Mike      3

Can anyone please help me with the query.

Upvotes: 1

Views: 2335

Answers (3)

LukStorms
LukStorms

Reputation: 29677

First add the column to the table.

ALTER TABLE yourtable
ADD [UID] INT NULL;

ALTER TABLE yourtable
ADD constraint fk_yourtable_uid_id foreign key ([UID]) references yourtable([Serial]);

Then you can update the UID with the minimum Serial ID per Name.

UPDATE t
SET [UID] = q.[UID]
FROM yourtable t
JOIN
(
  SELECT Name, MIN([Serial]) AS [UID]
  FROM yourtable
  GROUP BY Name
) q ON q.Name = t.Name
WHERE (t.[UID] IS NULL OR t.[UID] != q.[UID]) -- Repeatability

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Simply use dense_rank():

select t.*, dense_rank() over (order by name) as unique_id
from t;

If you want to maintain the original ordering by the first column, then you can use that:

select t.*, min(serial) over (partition by name) as unique_id
from t;

This will have gaps. You can combine these to get a unique, gap-less sequence:

select t.*, dense_rank() over (order by seqnum) as unique_id
from (select t.*, min(serial) over (partition by name) as seqnum
      from t
     ) t;

This method has the advantage that it is stable -- assuming that the first column is an identity column (so new names added over time would have higher serial values than existing names).

Upvotes: 2

JohnHC
JohnHC

Reputation: 11205

To select it on the fly, use this:

with CTE as
(
select name, dense_rank() over (order by name) as rn
from MyTable
)
select t1.id, t1.name, t2.rn
from MyTable t1
inner join CTE t2
on t2.name = t1.name

To bake it in, create a new table of names with an identity(1,0) column and add new names to this list (thrown together for inspiration)

create table name_uid
(
id      int identity(1,0) not null,
name    varchar(50)
)

insert into name_uid (name)
select distinct name 
from mytable

alter table mytable
add the_uid int

alter table
add constraint fk_name_uid foreign key (the_uid) references name_uid(id)

update t1
set t1.the_uid = t2.id
from Mytable t1
inner join name_uid t2
on t1.name = t2.name

create trigger tr_uid on MyTable
for insert
as
declare @name varchar
declare @exist int
declare @uid  int
begin
select @name = i.name from inserted i;
select @exist = case when exists (select 1 from name_uid where name = @name) then 1 else 0 end;
begin
if @exist = 0
insert into name_uid (name) values (@name);
end;
select @uid = select id from name_uid where name = @name;
update MyTable;
set the_uid = @uid;
go

Upvotes: 1

Related Questions