Finding duplicates that share information

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 2005 Forums

Transact-SQL (2005)

Finding duplicates that share information

Author

Topic

SLM09
Starting Member

31 Posts

Posted - 2009-10-20 : 14:02:56

Hi all,

I am going nuts with this one. I need a query that will return records that have a duplicate value but another value the same.

Since it's hard to describe, heres an example

name val1 val2
bob 1 1
bob 2 2
bob 3 2
jon 1 1
beth 1 1
beth 1 2
j 1 1
j 1 1

Here, I would want returned-

bob 2 2
bob 3 2
j 1 1
j 1 1

I want to return all records where the name is a duplicate AND val2 is a duplicate.

I should also mention there are plenty of other fields that are different, so I can just do a full dedup on the record.

Any help is really appreciated...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-20 : 14:06:53

[code]select t1.fields...
from yourtable t1
join (select name,val2
from yourtable
group by name,val2
having count(val1)>1)t2
on t2.name=t1.name
and t2.val2=t1.val2
[/code]

SLM09
Starting Member

31 Posts

Posted - 2009-10-20 : 14:58:18

That worked like a charm!

Thank you Visakh!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-20 : 15:11:50

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources