SQLTeam.com | Weblogs | Forums

Need to create uniuqe key

Hello Everyone,

We have set of records have same code , I want to assign unique key to identify the rows has same code. kindly provide the query.

e.g.

Code,Name,City,Contact
1,A,US,239878
1,B,US,456789
2,C,US,876876
2,D,CA,123432
2,A,ZT,45677

Output should be :

SNO,Code,Name,City,Contact
1,1,A,US,239878
2,1,B,US,456789
1,2,C,US,876876
2,2,D,CA,123432
3,2,A,ZT,45677

hi hope this helps

drop table #temp 

create table #temp(Code int ,Name varchar(1),City varchar(2),Contact int )

insert into #temp select 1,'A','US',239878
insert into #temp select 1,'B','US',456789
insert into #temp select 2,'C','US',876876
insert into #temp select 2,'D','CA',123432
insert into #temp select 2,'A','ZT',45677


select 'Data',* from #temp 

select 
      'SQL'
	, row_number() over(partition by Code order by Code) as rn 
	,  * 
from 
    #temp 

image