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
GROUP BY Model_Number
HAVING COUNT( * ) >1
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