Pulling duplicate records

[jabmod]

All,
I have a table CustInfo that stores customer information:FName, Lname, Address, City, State, ZipCode etc.

I would like to pull only the duplicate records. In this case those records having the same FName, LName and ZipCode

Thanks.

 

[mjp483]

Try this to return your duplicate records:

select
count(*),
FName,
LName,
ZipCode
FROM
YOURTABLE
GROUP BY
FName,
LName,
ZipCode
HAVING
Count(*) > 1

Mike

 

[jabmod]

Thanks a bunch. However, I need to still pull all the other columns such as Address, City, State to this report. I also need to pull duplicated rows instead of just one row for each duplicate set of records.


It is posible for other columns to be different between the records but as long as the FName, LName, and ZipCode are the same, they should be considered duplicates.

Thank you.

 

[mjp483]

It sounds like to satisfy this:

1) I also need to pull duplicated rows instead of just one row for each duplicate set of records

AND this:

2) I need to still pull all the other columns such as Address, City, State

you would:

select
FName,
LName,
ZipCode,
Address,
City,
State
FROM
YOURTABLE

Why did you ask to identify duplicates if you are going to include them all in your return set?

Mike

Mike

 

[jabmod]

The reason is to eliminate those that were not duplicated. E.g. If you have:

John Maxwell 111 crogo rd. Albany, NY 62000
John Maxwell 111 crogo rd. Albany, NY 62000
David Taylor 105 Baylor Dr. Baltimore, MD 23100
Jeff Daniels 200 crogo rd. Richmond, VA 34511
Jeff Daniels 1 Mayberry Dr. Richmond, VA 34511
Mary Kay 50 crogo rd. St. Louis, MO 63136

The result should be:

John Maxwell 111 crogo rd. Albany, NY 62000
John Maxwell 111 crogo rd. Albany, NY 62000
Jeff Daniels 200 crogo rd. Richmond, VA 34511
Jeff Daniels 1 Mayberry Dr. Richmond, VA 34511


Dulicates in FName, LName, and Zipcode.

Thanks.

 

[jabmod]

Still need some here pls.
Thanks.

 

[tc3596]

try this...

select tbl1.*
from
CustInfo AS tbl1
inner join (select FName, LName, ZipCode FROM CustInfo
group by FName, LName, ZipCode
having Count(*) > 1) AS tbl2
ON tbl1.fname = tbl2.fname and tbl1.lname = tbl2.lname and tbl1.zipcode = tbl2.zipcode