Duplicate rows in sql

[EmpHector]

hi,

TTITLE FORENAME SURNAME ADDRESS HOME_PHONE TOWN

mr Mohan Krishnan 42,MonksRD 1234567891 coventry,west midlands

null jason Rosss 13,CharterRD 1234567899 birmingham,west midlands

null Mohan Krishnan 42,MonksRD 1234567891 coventry,west midlands

mr Chris Hadland 30,watfordRD 5678912345 london,east midlands

mr Mohan Krishnan 42,MonksRD 4567891123 coventry,westmidlands



In this table the person Mohan is appeared three times and in the first row his title is mr and in the third row the title is null and in the 5 th row the title is mr and the home_phone is different from the other two rows, but all the three rows denote a same person. If this is the case then how will i find this duplicate record using a sql query..

my boss gave me 12 million records like this and he asked me to find all the duplicate records as i mentioned aboveand delete them.
please help me with this.

thanks in advance....

 

[presidentender]

This problem will vary based on what exactly constitutes a duplicate. I doubt that firstname lastname is sufficient, since you'll have more than one "John Smith," but just from your example there are other changes between records. In any case, you will need to use a "select" statement with a "group by" clause; you'll have to decide which fields to group by.

 

[brandonbull]

You're boss might want to supply you with some better business rules around defining "duplicates" before deleting records.

 

[HardcoreRobot]

you define what a dupe is, ill define your sql. hint, same name <> same person.

 

[brandonb]

I write software for bill collections, we define a dupe as someone who matches one of the 4 criteria:

Last Name + SSN
First Name + Last Name + Address 1 + Address 2 + Zip
First Name + Last Name + Phone
First Name + Last Name + Address 1 + Address 2 + City

But better to ask your boss!

 

[GeekDrew]

Originally posted by: brandonb
I write software for bill collections, we define a dupe as someone who matches one of the 4 criteria:

Last Name + SSN
First Name + Last Name + Address 1 + Address 2 + Zip
First Name + Last Name + Phone
First Name + Last Name + Address 1 + Address 2 + City

But better to ask your boss!

Holy crap... I can imagine quite a few real-life scenarios, that I've personally witnessed, that violate those conditions.

 

[EmpHector]

select Surname, count(*) from Master_Table where ADDRESS_1 like ADDRESS_1 and forename like forename and HOME_PHONE=HOME_PHONE group by Surname HAVING count(*) > 1;

i found this query could find the no of records that are duplicated how many times in the table wherer ADDRESS_1 and forename and home_phone matches with each other but i dont know how to list the records that are duplicated so that i could delete them ....

 

[HardcoreRobot]

Originally posted by: GeekDrew

Originally posted by: brandonb
I write software for bill collections, we define a dupe as someone who matches one of the 4 criteria:

Last Name + SSN
First Name + Last Name + Address 1 + Address 2 + Zip
First Name + Last Name + Phone
First Name + Last Name + Address 1 + Address 2 + City

But better to ask your boss!

Holy crap... I can imagine quite a few real-life scenarios, that I've personally witnessed, that violate those conditions.

And everyone freaking hates collectors, coincidence?!

 

[HardcoreRobot]

Originally posted by: EmpHector
select Surname, count(*) from Master_Table where ADDRESS_1 like ADDRESS_1 and forename like forename and HOME_PHONE=HOME_PHONE group by Surname HAVING count(*) > 1;

i found this query could find the no of records that are duplicated how many times in the table wherer ADDRESS_1 and forename and home_phone matches with each other but i dont know how to list the records that are duplicated so that i could delete them ....

unless im crazy, that query will only count number of surnames where a surname is repeated.

 

[EmpHector]

@above

ya i could find that it would just show me the no of records that are duplicated in the table but i need to the query to list all of those column with have address_1, forename and Surname matching with all thier column being listed.....

 

[HardcoreRobot]

alright, well lets say you do have records that are duplicate based on those 3 columns, which one of them do you want to keep? does it matter?

anyways try this:

select rhs.* from master_table lhs inner join master_table rhs on lhs.surname = rhs.surname and lhs.forename = rhs.forename and lhs.address_1 = rhs.address_1 and lhs.rowid <> rhs.rowid;

I think you will find it doesn't actually solve your problem, but thats what you are asking for...

That joins the table to itself where the record matches a different record on that criteria. The last part in the join clause keeps the record from matching itself. You will get some record explosion for the frequently duplicated values. And again, this doesn't solve the problem of choosing which records to keep and which ones to drop.

 

[KIAman]

This is why I stress validation on entry for my projects. Essentially, garbage in = garbage out.

In your case, there are several methods of identifying duplicates by simply counting the number of rows which match itself depending on which column criteria(s) you would like to match into a temp table. Then remove the rows from the original table which had a count greater than 2 then insert a row back from the temp table.

The hard part is

1. Determining what column(s) match equate to a duplicate
- don't be surprised to find name mismatches as well 'Mohan Krishnann' for example
2. Determining which duplicate row to keep as the "unique" row

And in the end, until the data entry portion is resolved, you will have a lifetime job on your hands.

If this were me, I wouldn't even take this job considering the information you have. I would explain the risks and only if the risks are signed off, I would go about removing duplicates. Good luck.

 

[imported_Dhaval00]

This probably is not going to help the OP, but I have dealt with a few projects where this was a business requirement (and realistically, desirable). Within the data warehousing realm, we have used Integration Services' Fuzzy Lookup transformation. We don't necessarily delete/cleanse the data ourselves: we ask the customers to "set" their cutoff threshold [for example, if the confidence and similarity is above 80%, treat the rows as duplicate].

Again, not a full-proof solution, but quite effective if you're dealing with millions of rows.

 

[txrandom]

Originally posted by: brandonb
I write software for bill collections, we define a dupe as someone who matches one of the 4 criteria:

Last Name + SSN
First Name + Last Name + Address 1 + Address 2 + Zip
First Name + Last Name + Phone
First Name + Last Name + Address 1 + Address 2 + City

But better to ask your boss!

Damn it, why am I getting my Dad's bills.

 

[EmpHector]

hello guys

i got the answer in this link

http://support.microsoft.com/kb/139444

the creiteria u r saying was told by my boss.....i got it and i m fine now ....

thanks for help everyone.....

 

[BoberFett]

Originally posted by: KIAman
This is why I stress validation on entry for my projects. Essentially, garbage in = garbage out.

I'm guessing in this case there was no input therefore no validation. It's a bulk mailing list they purchased, if I had to guess.

 

[KIAman]

Originally posted by: BoberFett

Originally posted by: KIAman
This is why I stress validation on entry for my projects. Essentially, garbage in = garbage out.

I'm guessing in this case there was no input therefore no validation. It's a bulk mailing list they purchased, if I had to guess.

If that were so, I'd run this through an address scrubber based off a standardized database, like USPS. Looking at the OPs list of examples, I don't think that was done (west midlands, westmidlands, for ex).