How I create a table without singles records in MySQL

Question

For example, I have the next table (IN MySQL)

| a | 1002 |
| b | 1002 |
| c | 1015 |
| a | 1005 |
| b | 1016 |
| a | 1106 |
| d | 1006 |
| a | 1026 |
| f | 1106 |

I want to select the objects that are duplicates.

| a | 1002 |
| a | 1106 |    
| a | 1026 | 
| a | 1005 |
| b | 1002 |   
| b | 1016 |

Thank you

Possible duplicate, already answered here: http://stackoverflow.com/questions/854128/find-duplicate-records-in-mysql — artahian, Jun 14 '13 at 15:24

score 0 · Answer 1 · answered Jun 14 '13 at 15:27

0

If I understand the question, you want to select rows where the number column is duplicated. One way to do it is to join against a subquery returns a list of number values that occur more than once.

SELECT letter, number
FROM myTable A
INNER JOIN (
  SELECT number
  FROM myTable
  GROUP BY number
  HAVING COUNT(*) > 1
) B ON A.number = B.number

answered Jun 14 '13 at 15:27

Ed Gibbs

25,924
4
46
69

Your query won't return the results a user is expecting. You should do grouping and joining on a letter rather than on a number. – jonasnas Jun 14 '13 at 15:40

score 0 · Answer 2 · answered Jun 14 '13 at 15:48

As an alternative, if you want the list of all values where there are duplicates, you can use group_concat:

select col1, group_concat(col2)
from t
group by col1
having count(*) > 1

This does not return the exact format you want. Instead it would return:

| a | 1002,1106,1026,1005 |
| b | 1002,1016 |

But you might find it useful.

How I create a table without singles records in MySQL

2 Answers2