user580950 user580950 - 1 year ago 59
SQL Question

Query Doesnt return Duplicate records - Mysql

My following query doesn't return duplicate records(Model_Number), is there a reason.Should return all duplicate model_number, with the following query it returns all unique.The Mysql has 2,17,092 records in total

SELECT AZ_Code, Model_Number
FROM dumpdata
GROUP BY Model_Number

Answer Source

The previous answers should work, but I would expect this one to be much faster (as MySQL usually JOINs to subqueries more effectively than it uses them elsewhere, such as in WHERE clauses).

SELECT dd.AZ_Code, dd.Model_Number
FROM dumpdata AS dd
   SELECT   Model_Number
   FROM     dumpdata
   GROUP BY Model_Number
   HAVING COUNT(*) > 1
) AS repeatedModels 
ON dd.Model_Number = repeatedModels.Model_Number
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download