SQL server row-number to tag duplicate rows

Question

I am trying to add a row number to duplicate records.

declare @tbl table
(col1 varchar(10),col2 varchar(10) )

insert into @tbl values ('a1','b1')

insert into @tbl  values('a1','b1')

insert into @tbl values ('a1','b2')


insert into @tbl  values('a2','b2')

insert into @tbl  values('a2','b2')
insert into @tbl  values('a2','b2')
insert into @tbl  values('a3','b3')
insert into @tbl  values('a3','b3')


select col1,col2,ROW_NUMBER() OVER(ORDER BY col1,col2 ) AS id from @tbl

The below is the expected result

declare @tbl1 table
(col1 varchar(10),col2 varchar(10),id int )

insert into @tbl1 values ('a1','b1',1)

insert into @tbl1  values('a1','b1',2)

insert into @tbl1 values ('a1','b2',1)


insert into @tbl1  values('a2','b2',1)

insert into @tbl1  values('a2','b2',2)
insert into @tbl1  values('a2','b2',3)
insert into @tbl1  values('a3','b3',1)
insert into @tbl1 values('a3','b3',2)

select * from @tbl1

score 4 · Accepted Answer · answered Oct 21 '14 at 21:05

4

Try this you need to use partition by

SELECT col1,
       col2,
       Row_number()
         OVER(
           partition BY col1, col2
           ORDER BY col1, col2 ) AS id
FROM   @tbl

answered Oct 21 '14 at 21:05

Pரதீப்

91,748
19
131
172

Thank you this helped me resolve my issue. Will accept this as answer after 3 minutes. Because it is not letting me to accept now – Senthilkumar Elangovan Oct 21 '14 at 21:09

score 2 · Answer 2 · answered Oct 21 '14 at 21:02

2

row_number will always give every row within the window a different value. If you want to detect duplicates, you need to use rank instead:

SELECT col1, col2, 
       RANK() OVER (PARTITION BY col1, col2 ORDER BY col1, col2) AS id 
FROM   @tbl

Or to query just the duplicates:

SELECT col1, col2
FROM   (SELECT col1, col2, 
               RANK() OVER (PARTITION BY col1, col2 ORDER BY col1, col2) AS id 
        FROM   @tbl) t
WHERE  id > 1

answered Oct 21 '14 at 21:02

Mureinik

297,002
52
306
350

2

The key is adding PARTITION BY, not changing from ROW_NUMBER() to RANK(). – Aaron Bertrand Oct 21 '14 at 21:09

SQL server row-number to tag duplicate rows

2 Answers2