Reputation: 638
I have a table with 4 columms like below
var1 var2 var3 ran
a a a 1
b a a 2
c a a 3
d b b 4
e c c 1
Here based on the value in columns var2 and var3, it is consider as duplicates. 4th column is the row number for the duplicates. I need to re-initialise the row-number (update) on non-duplicate records.
Expected result will be (note that d b b 4
became d b b 1
):
var1 var2 var3 ran
a a a 1
b a a 2
c a a 3
d b b 1
e c c 1
Upvotes: 0
Views: 171
Reputation: 139010
declare @T table
(
var1 char(1),
var2 char(1),
var3 char(1),
ran int
)
insert into @T values
('a', 'a', 'a', 1),
('b', 'a', 'a', 2),
('c', 'a', 'a', 3),
('d', 'b', 'b', 4),
('e', 'c', 'c', 1)
;with C as
(
select ran,
row_number() over(partition by var2, var3 order by var1) as rn
from @T
)
update C
set ran = rn
select *
from @T
Result:
var1 var2 var3 ran
---- ---- ---- -----------
a a a 1
b a a 2
c a a 3
d b b 1
e c c 1
Upvotes: 2