Clever Geek Handbook

SQL Server line number to mark duplicate lines

I am trying to add a line number for 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

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
+3


source to share


2 answers


Try this, you need to use the section



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


source


row_number

will always give each line in the window a different value. If you want to detect duplicates, you need to use instead rank

:

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


Or to request only 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
+2


source






More articles:


All Articles