CODEWITHSUNDEEP
mysql
Busted
Busted

Reputation: 147

How can I find duplicate with same word and same topic?

I have a table like below. Table name is 'Test'.

+----+----------+----------+
| id | word     | topic    |
+----+----------+----------+
|  1 | plus     | math     |
|  2 | minus    | math     |
|  3 | multiple | math     |
|  4 | minus    | math     |
|  5 | cpu      | computer |
|  6 | click    | computer |
|  7 | monitor  | computer |
|  8 | cpu      | computer |
|  9 | multiple | computer |
+----+----------+----------+

How can I find duplicate word with same topic?

I want result like below.

 +----+----------+----------+
 | id | word     | topic    |
 +----+----------+----------+
 |  2 | minus    | math     |
 |  4 | minus    | math     |
 |  5 | cpu      | computer |
 |  8 | cpu      | computer |
 +----+----------+----------+

Upvotes: 0

Views: 29

Answers (3)

Strawberry
Strawberry

Reputation: 33945

SELECT DISTINCT x.* 
          FROM test x 
          JOIN test y 
            ON y.id <> x.id 
           AND y.word = x.word 
           AND y.topic = x.topic;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

If you don't need separate rows for each id, you can do:

select word, topic, group_concat(id)
from t
group by word, topic
having count(*) > 1

Aggregation can be rather expensive, so if you do want the original rows, exists is an option:

select t.*
from t
where exists (select 1
              from t t2
              where t2.word = t.word and t2.topic = t.topic and t2.id <> t.id
             );

For performance, you want an index on (word, topic, id).

Upvotes: 0

juergen d
juergen d

Reputation: 204904

select *
from your_table
where word in
(
    select word
    from your_table
    group by word, topic
    having count(*) > 1
)

Upvotes: 1

Related Questions