Display duplicate records in MySQL table

Question

I have a MySQL table that has about 30k records, of which 2k I estimate are duplicate.

How do I craft a query to show duplicates that share ALL of the following columns STREET_NUMBER, STREET_NAME, UNIT_NUMBER, ZIP_CODE but another column called MLS_ID that are NOT the same?

I only want to see the records where there is a complete match and the MLS_ID is different.

Should the last sentence say "I want the records where there is a complete match EXCEPT FOR the MLS_ID"? — Matt Fenwick
– Matt Fenwick, Commented Dec 9, 2011 at 18:36

Nitin Midha · Accepted Answer · 2011-12-09 18:27:38Z

1

I think this should work (untested)

SELECT *  
  FROM Table1 T
 INNER JOIN
          (
            SELECT STREET_NUMBER, STREET_NAME, UNIT_NUMBER, ZIP_CODE
              FROM Table1 T1
             GROUP BY STREET_NUMBER, STREET_NAME, UNIT_NUMBER, ZIP_CODE
            HAVING COUNT(DISTINCT MLS_ID) > 1
          ) T2 ON T.STREET_NUMBER = T2.STREET_NUMBER AND T.STREET_NAME = T2.STREET_NAME AND         T.UNIT_NUMBER = T2.UNIT_NUMBER AND T.ZIP_CODE = T2.ZIP_CODE

answered Dec 9, 2011 at 18:27

Nitin Midha

2,27820 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Will · Accepted Answer · 2011-12-09 18:18:58Z

1

Try this(untested):

SELECT * FROM table t1 INNER JOIN table t2 
  USING(STREET_NUMBER, STREET_NAME, UNIT_NUMBER, ZIP_CODE) 
  WHERE t1.MLS_ID != t2.MLS_ID;

answered Dec 9, 2011 at 18:18

Will

9052 gold badges9 silver badges19 bronze badges

1 Comment

Nitin Midha Over a year ago

If we have 3 different records with different MLS_ID but same street_number,streetname etc. Then this query will return 6 records ... I havent tested it but, that seems logical .....

Matt Fenwick · Accepted Answer · 2011-12-09 18:25:19Z

1

You could do a group-by, and take only those groups where the count is greater than 1 (i.e. where there are duplicates):

select 
  <grouping columns>  -- put STREET_NUMBER, STREET_NAME, etc. here
from
  (select distinct * from <tablename>)
group by
  <grouping columns>  -- and here!
having 
  count(*) > 1

Just don't put MLS_ID in the group-by clause. This assumes that MLS_ID is the only non-grouping column.

edited Dec 9, 2011 at 18:25

answered Dec 9, 2011 at 18:17

Matt Fenwick

49.3k24 gold badges130 silver badges198 bronze badges

Collectives™ on Stack Overflow

Display duplicate records in MySQL table

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related