Find Duplicate Emails using Row_Number

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 282
Consider the below table

Declare @t Table(name varchar(20), email varchar(20))
insert into @t values ('name1', '1stEmail@email.com')
insert into @t values ('name1', '2ndEmail@email.com')
insert into @t values ('name1', '3rdEmail@email.com')
insert into @t values ('name2', '1stEmail@email.com')
insert into @t values ('name3', '1stEmail@email.com')
insert into @t values ('name3', '2ndEmail@email.com')
insert into @t values ('name4', '1stEmail@email.com')


RowNumber approach to find the duplicate email entries

Select distinct name,count(email)+1
From
(
Select
Rn = Row_Number() Over(Partition By name Order By (Select 1))
,name,email
From @t
)x
Where Rn > 1
Group By name

/* Result */
name EmailCount
name1 3
name3 2


We are using the partition by clause to perform the partition which will yield the below

Rn	name	email
1 name1 1stEmail@email.com
2 name1 2ndEmail@email.com
3 name1 3rdEmail@email.com
1 name2 1stEmail@email.com
1 name3 1stEmail@email.com
2 name3 2ndEmail@email.com
1 name4 1stEmail@email.com


From the result generated we can make out that, Rn with value > 1 has multiple entries.

Comments or Responses

Login to post response