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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 duplicate rows

Author  Topic 

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-05-03 : 10:43:44
Hi,

Here's the table, T

id email
1 1@1.com
2 2@3.com
3 1@1.com
4 1@1.c0m
5 2@3.com

How do i get all the duplicate emails(like I should get 3 records for 1@1.com)?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-03 : 10:50:47
[code]
SELECT id,email
FROM
(
SELECT COUNT(1) OVER (PARTITION BY email) AS Occ,*
FROM Table
)t
WHERE Occ > 1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-03 : 10:51:01
SELECT email,count(1) from T group by Email having count(1)>1

Cheers
MIK
Go to Top of Page
   

- Advertisement -