Query doesnt return duplicate records - Mysql

Question

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

Since you are grouping it does returned the duplicates but grouped(those unique model_number are in fact the ones which are duplicates).If you want too see the duplicates as they are JOIN on this subquery — Mihai
– Mihai, Commented Jun 9, 2016 at 16:53
@Mihai I do not understand what do you mean by "As they are JOIN on this subquery " — TSCAmerica.com
– TSCAmerica.com, Commented Jun 9, 2016 at 16:55

Uueerdo · Accepted Answer · 2016-06-09 17:43:36Z

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

edited Jun 9, 2016 at 17:43

answered Jun 9, 2016 at 17:14

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

TSCAmerica.com Over a year ago

I get an error when I run the query #1052 - Column 'Model_Number' in field list is ambiguous

Uueerdo Over a year ago

Have you indexed Model_Number? and what is the field's data type?

TSCAmerica.com Over a year ago

let me reboot the server and try it again

TSCAmerica.com Over a year ago

Model_Number - varchar(255)

Uueerdo Over a year ago

When dealing with larger numbers of rows, indexing can help with grouping, ordering, conditions in WHERE clauses and used for JOINs; and when dealing with character data the benefits can be even more pronounced; comparing 2 million unindexed integers is a lot faster than comparing 2 million strings.

Mureinik · Accepted Answer · 2016-06-09 16:56:37Z

0

The group by clause means you want just one result per unique value you group by (and the fact you can query fields not in it is a MySQL oddity). You could work around with with the use of the in operator:

SELECT AZ_Code, Model_Number
FROM   dumpdata
WHERE  Model_Number IN (SELECT   Model_Number
                        FROM     dumpdata
                        GROUP BY Model_Number
                        HAVING   COUNT(*) > 1)

answered Jun 9, 2016 at 16:56

Mureinik

316k54 gold badges400 silver badges405 bronze badges

1 Comment

TSCAmerica.com Over a year ago

This query freezes phpmyadmin is there another way to run the query and export to csv

Mihai · Accepted Answer · 2016-06-09 16:56:54Z

0

One way of seeing the duplicates

SELECT d.AZ_Code, d.Model_Number
FROM dumpdata d
WHERE
(SELECT COUNT(*) FROM dumpdata  WHERE d.Model_Number=dumpdata.Model_Number)>1

answered Jun 9, 2016 at 16:56

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

3 Comments

TSCAmerica.com Over a year ago

This query freezes phpmyadmin, is there another way to run the query

Mihai Over a year ago

@user580950 How many rows do you have?Also you have to be careful,cancelling a query in phpmyadmin doesnt means the query will also be cancelled on the server.

TSCAmerica.com Over a year ago

2,17,092 records in total, I rebooted the server so that phpmyadmin works

Collectives™ on Stack Overflow

Query doesnt return duplicate records - Mysql

3 Answers 3

5 Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related