query for duplicates - 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

query for duplicates

Author

Topic

indio
Starting Member

7 Posts

Posted - 2007-03-08 : 16:52:49

Hi All.

I have a Table with records and some of them are actually multiple records. I need to query SQL 2005 (compare) field values on more than one column to return these records.

I am an absolute beginner and need some guidance on how to start on this. Can anyone advise.

Thanks.

-Indio

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-08 : 17:01:15

Have a look at the first post (the FAQ) in this forum for info on how to post sample data and required output, we can't guess what you are looking for.

indio
Starting Member

7 Posts

Posted - 2007-03-08 : 17:23:09

snSQL:

Say I have a Table named Member with columns:

FirstName LastName DOB SSN Address

John Smith 01/01/2007 123456789 Apt#209
Josh Smith 01/01/2007 123456789 Apt#8765
Pat Lopez 06/01/1956 287654098 Apt#7654

If the first 2 records are duplicates, How do i query to compare the DOB and SSN values in the table to return them.

Thanks.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-03-08 : 17:34:09

select a.firstname,a.lastname,a.dob,a.ssn,a.address
from member a
join (
select dob,ssn
from member
group by dob, ssn
having count(*)>1)b
on a.dob=b.dob and a.ssn=b.ssn

--------------------
keeping it simple...

indio
Starting Member

7 Posts

Posted - 2007-03-08 : 17:42:39

Jen:

Thank you so much !

-Indio

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources