Locate Duplicate ids in MySQL table

Posted on 2011-05-13
Last Modified: 2012-05-11
I have a table called MLSIDS that has a column called ListingID.  This column is not the primary key.  1.) What query can I issue to show me all duplicate ListingIDs in the table?
2.) What query can I issue to give me a count of all duplicates?
Question by:pda4me
    LVL 8

    Expert Comment

    select ListingID, count(ListingID) from MLSIDS group by ListingID

    Open in new window


    Author Comment

    close, I need to just show the ids that are duplicates...this shows all 25K records.
    LVL 8

    Accepted Solution

    select ListingID, count(ListingID) as idcnt from MLSIDS group by ListingID having idcnt > 1

    Open in new window


    Author Closing Comment

    Excellent, thanks a bunch!

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
    Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit ( and similar technologies have enjoyed wide adoption, making it possib…
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    The viewer will learn how to count occurrences of each item in an array.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now