user580950 user580950 - 5 months ago 8
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
HAVING COUNT( * ) >1

Answer

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
INNER JOIN (
   SELECT   Model_Number
   FROM     dumpdata
   GROUP BY Model_Number
   HAVING COUNT(*) > 1
) AS repeatedModels 
ON dd.Model_Number = repeatedModels.Model_Number
Comments