duplicate records problem SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

duplicate records problem SQL

Author

Topic

Johnnybax
Starting Member

3 Posts

Posted - 2012-07-26 : 21:11:58

Table

Billingid billingemail
1 abc@abc.com
2 bob@bob.com
3 john@john.com
4 joe@joe.com
5 bob@bob.com
6 miller@miller.com
7 john@john.com

I need to get records which are repeated in billingemail column.
For example in above table I need to get "john@john.com" and " bob@bob.com" which are repeated in the table.

Any way to do it with a simple query?

Thanks for the help.

chriztoph
Posting Yak Master

184 Posts

Posted - 2012-07-26 : 22:18:31

try this,

select distinct(billingemail) as billingemail from <your table>
group by billingemail
having Count(billingemail) > 1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-27 : 02:53:02

DISTINCT isn't needed on a grouped column.
And you can also get a counter on how many duplicates for a mailaddress exists.

select billingemail, count(*) as cnt from <your table>
group by billingemail
having Count(*) > 1

No, you're never too old to Yak'n'Roll if you're too young to die.

vijayan.vinu3
Starting Member

19 Posts

Posted - 2012-07-27 : 08:07:32

There are many ways to do it. This is another one


Select BillingId, BillingEmail From 
	(Select *, ROW_NUMBER() Over (Partition By BillingEmail Order By(Select NULL) ) As rn From Ex) As a
Where rn > 1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-27 : 08:09:51

quote:
Originally posted by vijayan.vinu3

There are many ways to do it. This is another one

Select BillingId, BillingEmail From
(Select *, ROW_NUMBER() Over (Partition By BillingEmail Order By(Select NULL) ) As rn From Ex) As a
Where rn = 1

OP needs to get records which are repeated in billingemail column.

No, you're never too old to Yak'n'Roll if you're too young to die.

vijayan.vinu3
Starting Member

19 Posts

Posted - 2012-07-27 : 08:22:51

quote:
Originally posted by webfred

quote:
Originally posted by vijayan.vinu3

There are many ways to do it. This is another one

Select BillingId, BillingEmail From
(Select *, ROW_NUMBER() Over (Partition By BillingEmail Order By(Select NULL) ) As rn From Ex) As a
Where rn = 1

OP needs to get records which are repeated in billingemail column.

No, you're never too old to Yak'n'Roll if you're too young to die.

Yes Web...that was an error...I have already edited it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources