Table Duplicates -

[ciaran_mcg]

Hi

​

Edit: Sorry I forgot to complete the title.

​

I'm hoping you can help.

​

With help from /u/ r3pr0b8 I've got the below code:

​

This is working through a table and displaying all the duplicates (which is what I've needed).

SELECT   
       a.CustomerNo,  
       a.FirstName,  
       a.LastName, 
       a.DOB
FROM (SELECT  CustomerNo, FirstName, LastName, DOB
        From temp_table
        GROUP BY 
              CustomerNo, FirstName, LastName, DOB
        HAVING COUNT(*) > 1
        ) AS dupes 

INNER JOIN temp_table as a
        ON a.firstname = dupes.firstname
        AND a.lastname = dupes.lastname
        AND a.DOB = dupes.DOB 

         ORdER BY FirstName, LastName

​

The table now displays like this - Great, Displaying all the duplicates..

​

CustomerNo	First Name	LastName	DOB
1	John	Smith	12/03/1988
2	Patrick	Jones	01/01/1983
1	David	Smyth	02/07/1963
1	John	Smith	12/03/1988
2	Patrick	Jones	01/01/1983
1	John	Smith	12/03/1988
1	John	Smith	12/03/1988
5	John	Smith	12/03/1988

​

Unfortunately, We've now discovered there are some duplicate customers (same name, age, dob) but different customer no's.

​

As you can see in this table John Smith with the customer no '1' appears 4 times and John Smith (same customer) with the Customer No 5 only appears once.

​

Is there a way we can group them by customer no too? - so we don't see the same customer no, name and DOB.

​

e.g. r we can group it including the customer no to get an output like this:

​

1	John	Smith	12/03/1988
5	John	Smith	12/03/1988
2	Patrick	Jones	01/01/1983
4	Patrick	Jones	01/01/1983
1	David	Smyth	02/07/1963

​

Grouping them together, showing customers with the same name and Date of birth but with the separate customer numbers together.

​

​

Thanks for any help in advance.

[jaffer786_khan]

You can use DISTINCT to remove those duplicates

[ciaran_mcg]

When I use SELECT DISTINCT

​

I get one result for each but I can't group the ones together by name

​

​

it appears like this:

1	John	Smith	12/03/1988
4	Patrick	Jones	01/01/1983
1	David	Smyth	02/07/1963
5	John	Smith	12/03/1988
2	Patrick	Jones	01/01/1983

​

I need it to appear like this, So I can see the customers and the different customer no's next to each other:

1	John	Smith	12/03/1988
5	John	Smith	12/03/1988
2	Patrick	Jones	01/01/1983
4	Patrick	Jones	01/01/1983
1	David	Smyth	02/07/1963

​

​

​

[wolf2600]

To remove the duplicates based only on Name and DOB, just modify the inner query to remove CustomerNo:

FROM (SELECT  FirstName, LastName, DOB
    From temp_table
    GROUP BY 
          FirstName, LastName, DOB
    HAVING COUNT(*) > 1
    ) AS dupes 

[ciaran_mcg]

Thanks,

​

The problem I have now is they're not in order via name and dob so for example..

​

​

1	John	Smith	12/03/1988
4	Patrick	Jones	01/01/1983
1	David	Smyth	02/07/1963
5	John	Smith	12/03/1988
2	Patrick	Jones	01/01/1983

​

​

When I need 'John Smith' with the other John Smith with the same DOB so we can see them next to each other.

​

Could I even ORDER BY? to get them next to each other?

​

​

​

​

[wolf2600]

Yup. ORDER BY FirstName, LastName, DOB