Select the fields are duplicated in mysql

Question

Assuming that I have the below customer_offer table.

My question is:

How to select all the rows where the key(s) are duplicated in that table?

+---------+-------------+------------+----------+--------+---------------------+
| link_id | customer_id | partner_id | offer_id | key    | date_updated        |
+---------+-------------+------------+----------+--------+---------------------+
|       1 |          99 |         11 |       14 | mmmmmq | 2011-09-21 12:40:46 |
|       2 |         100 |         11 |       14 | qmmmmq | 2011-09-21 12:40:46 |
|       3 |         101 |         11 |       14 | 8mmmmq | 2011-09-21 12:40:46 |
|       4 |          99 |         11 |       14 | Dmmmmq | 2011-09-21 12:59:28 |
|       5 |         100 |         11 |       14 | Nmmmmq | 2011-09-21 12:59:28 |
+---------+-------------+------------+----------+--------+---------------------+

UPDATE:

Thanks so much for all your answer. There are many answers are good. Now I got the solution to do.

xdazz · Accepted Answer · 2011-09-28T10:30:38.243

3

select * 
from customer_offer 
where key in 
(select key from customer_offer group by key having count(*) > 1)

Update: As mentioned from @Scorpi0, if with a big table, it is better to use join. And from mysql6.0 the new optimizer will convert this kind of subqueries into joins.

edited Sep 28 '11 at 10:30

answered Sep 28 '11 at 09:59

xdazz

158,678
38
247
274

Warning with big table and many duplicate, this query could not be optimal – Cyril Gandon Sep 28 '11 at 10:12
See here : http://stackoverflow.com/questions/141278/subqueries-vs-joins or http://stackoverflow.com/questions/2577174/sql-join-vs-subquery – Cyril Gandon Sep 28 '11 at 10:23
@Scorpi0: Thanks you. Now, my knowledge is more better. – vietean Sep 28 '11 at 10:55

score 1 · Answer 2 · answered Sep 28 '11 at 09:55

1

Self join

SELECT * FROM customer_offer c1 inner join customer_offer c2
on c1.key = c2.key

or group by the field then take when count > 1

SELECT COUNT(key),link_id FROM customer_offer c1
group by key, link_id
having COUNT(Key) > 1

answered Sep 28 '11 at 09:55

Rami Alshareef

7,015
12
47
75

score 1 · Answer 3 · answered Sep 28 '11 at 09:59

1

SELECT DISTINCT c1.* 
FROM customer_offer c1 
    INNER JOIN customer_offer c2
        ON c1.key = c2.key
        AND c1.link_id != c2.link_id

Assuming link_id is a primary key.

answered Sep 28 '11 at 09:59

Cyril Gandon

16,830
14
78
122

+2: Thanks you so much for your confirm to the answer which is set correctly. – vietean Sep 28 '11 at 10:55

Matt Seymour · Answer 4 · 2011-09-28T09:58:58.700

0

There are many threads on the mysql website which explains how to do this. This link will explain how to do this using mysql: http://forums.mysql.com/read.php?10,180556,180567#msg-180567

As a brief example the code below is from the link with a slight modification which better suits your example.

SELECT *
FROM tbl
GROUP BY key
HAVING COUNT(key)>1;

You can also use a joing which is my prefered method, as this removes the slower count method:

SELECT * 
FROM this_table t 
inner join this_table t1 on t.key = t1.key

edited Sep 28 '11 at 09:58

answered Sep 28 '11 at 09:53

Matt Seymour

8,880
7
60
101

This will not select all rows where `key` is duplicated. It will select a *singe* row for each duplicated key. – Ben Lee Sep 28 '11 at 09:59

score 0 · Answer 5 · answered Sep 28 '11 at 09:58

Use a sub-query to do the count check, and the main query to select the rows. The count check query is simply:

SELECT `link_id` FROM `customer_offer` GROUP BY `key` HAVING COUNT(`key`) > 1

Then the outer query will use this by joining into it:

SELECT customer_offer.* FROM customer_offer
INNER JOIN (SELECT `link_id` FROM `customer_offer` GROUP BY `key` HAVING COUNT(`key`) > 1) AS count_check
ON customer_offer.link_id = count_check.link_id

score 0 · Answer 6 · answered Sep 28 '11 at 13:27

0

SELECT link_id, key, count(key) as Occurrences 
FROM table 
GROUP BY key
HAVING COUNT(key)>1;

answered Sep 28 '11 at 13:27

Ghazanfar Mir

3,493
2
26
42

Select the fields are duplicated in mysql

UPDATE:

6 Answers6