How to list all duplicate values using MySQL?

Question

I have a table with the following fields:

 id | domainname | domain_certificate_no | keyvalue

An example for the output of a select statement can be as:

 id    |  domainname               |  domain_certificate_no      |  keyvalue
 ===============================================================================
'57092', '02a1fae.netsolstores.com', '02a1fae.netsolstores.com_1', '55525772666'
'57093', '02a1fae.netsolstores.com', '02a1fae.netsolstores.com_2', '22225554186'
'57094', '02a1fae.netsolstores.com', '02a1fae.netsolstores.com_3', '22444356259'
'97168', '02aa6aa.netsolstores.com', '02aa6aa.netsolstores.com_1', '55525772666'
'97169', '02aa6aa.netsolstores.com', '02aa6aa.netsolstores.com_2', '22225554186'
'97170', '02aa6aa.netsolstores.com', '02aa6aa.netsolstores.com_3', '22444356259’

The field domain_certificate_no is unique. I used group by to find the repeated keyvalue but what I actually need is not only how many occurrence for the keyvalue but also, I need to list the repeated value one by one along with the other fields in each record this value occurred. The thing that I could not achieve with group by. How can I do this.

Since you insist on repeating this data in every question, it might help if you at least once indicated an example of the output you'd like to get, as well as what you've tried to do so far that isn't working. — Ken White
– Ken White, Commented Aug 8, 2012 at 1:58
What would be nice is if you provided your sample data as a MySQL insert statement that I could just paste into MySQL to test some sample queries for you. — Adrian Cornish
– Adrian Cornish, Commented Aug 8, 2012 at 2:44

Adrian Cornish · Accepted Answer · 2012-08-08 02:04:49Z

1

Not tested but I think this would work just off the top of my head

SELECT * FROM table 
    WHERE keyvalue IN (
      SELECT keyvalue FROM table GROUP BY keyvalue HAVING COUNT(*) > 1
    )

edited Aug 8, 2012 at 2:04

answered Aug 8, 2012 at 1:57

Adrian Cornish

24k14 gold badges64 silver badges81 bronze badges

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

Comments

John Woo · Accepted Answer · 2012-08-08 02:56:32Z

1

an alternative from the others' answer is the INNER JOIN from a derived query.

SELECT  a.*
FROM    myTable a
            INNER JOIN
            (
                SELECT keyValue
                FROM myTable
                GROUP BY keyValue
                HAVING COUNT(keyValue) > 1
            ) b 
                ON a.keyValue = b.keyValue

answered Aug 8, 2012 at 2:56

John Woo

265k70 gold badges509 silver badges500 bronze badges

1 Comment

Adrian Cornish Over a year ago

Without testing and not being a DBA is that more efficient generally than the query I answered with

Hawili · Accepted Answer · 2012-08-08 02:00:07Z

0

Didn't test the code, but it should help you more ;)

select group_concat(id) as all_id, group_concat(domainname) as all_domains, group_concat(domain_certificate_no) as all_certificats , keyvalue, count(keyvalue) from table x
group by keyvalue
order by count(keyvalue)

answered Aug 8, 2012 at 2:00

Hawili

1,65911 silver badges15 bronze badges

1 Comment

Chris Henry Over a year ago

If there are more than 1024 values in the group, you'll need to change the group_concat_max_len value in the my.cnf. Also, this could be really, really hard to read.

Collectives™ on Stack Overflow

How to list all duplicate values using MySQL?

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related