Sunday, 11 August 2013

Display all duplicate records based multiple columns

Display all duplicate records based multiple columns

I'm very new to mySQL and I have tried searching all over the web and in
stackoverflow as well to find a solution to probably what is a very simple
query.
I have a table called Customer data, which has columns such as ID, first
name, surname, Address and date of birth. I want to run a query in mySQL
which will identify and display in groups all columns where first name,
last name, address and DOB is identical for 2 or more records. The key
thing to note here that ID is never duplicated and therefore I need to get
out the IDs for each duplicated record.
I have seen many sample queries where COUNT(*) is used but this just
displays 1 single record and the occurence of the same record in the count
column rather than bringing out the 2nd duplicated record.
A sample query I have run is this:
SELECT Uniq_Cust, Address, FirstName, LastName, DOB, COUNT(*) AS count1
FROM customerdata GROUP BY FirstName, LastName, DOB, Address HAVING count1
> 1 ORDER BY count1 ASC ;
I hope I have explained what I am trying to achieve clearly. Many thanks
for your help!

No comments:

Post a Comment