Complicated Duplicate Records MySQL Query

I have a table that I know has duplicate records.  The columns are named:STREET_NUMBER, STREET_NAME, STREET_TYPE, STREET_DIRECTION, UNIT_NUMBER

There is also a column called MLS_ID that makes this query difficult.

What is the proper SQL syntax to compare the address columns and display records where the MLS_ID do NOT match but the other columns DO match.

pda4meAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PranjalShahCommented:

SELECT a.* FROM table_name AS a, table_name AS b WHERE a.street_number = b.street_number AND a.mls_id!=b.mls_id //you can add as many conditions here

Open in new window

nemws1Database AdministratorCommented:
PranjalShah has posted a query that most people would - however, it doesn't work correctly.  If you have 3 duplicated entries,  it will appear *6* times in this query (since for each of the MLS_ID values will not equal *two* of the other MLS_ID values).  It's exponential - the more duplicated the rows, the much more you'll see those records appear.

The best method (although I personally dislike sub-queries) is to use IN with a sub-select:
SELECT MLS_ID
FROM table_name
WHERE MLS_ID NOT IN (
  SELECT MAX(MLS_ID)
  FROM table_name
  GROUP BY STREET_NUMBER, STREET_NAME, STREET_TYPE, STREET_DIRECTION, UNIT_NUMBER
)
;

Open in new window

This example *excludes* the latest duplicate entry (change MAX to MIN to keep the earliest entry), so the IDS returned from it can be immediately deleted from the table.  However, for tables with many rows, it can be inefficient and slow.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.