CODEWITHSUNDEEP
mysqlsql
Sankar
Sankar

Reputation: 59

Finding duplicates from MySql

lets say I have a validations table like below.

     validation_id    scenario_id
     100              1
     101              1
     102              1
     103              2
     104              2
     100              3
     101              3
     102              3

In the above table if we observe the scenario id's 1 and 3 have the same validation Id's 100, 101 and 102.

In this scenario I can say scenario_id=1 is a duplicate of scenario_id=3.

can I get a MySql query to find such duplicates.

Thanks in advance.

Upvotes: 0

Views: 52

Answers (2)

AntoineFe
AntoineFe

Reputation: 33

SELECT validation_ids, GROUP_CONCAT(scenario_id SEPARATOR ', ') AS scenario_ids
FROM (

    SELECT scenario_id, GROUP_CONCAT(validation_id ORDER BY validation_id SEPARATOR ', ') AS validation_ids
    FROM (

        SELECT * FROM (
        SELECT      100 validation_id, 1 scenario_id
        UNION SELECT     101 validation_id, 1 scenario_id
        UNION SELECT     102 validation_id, 1 scenario_id
        UNION SELECT     103 validation_id, 2 scenario_id
        UNION SELECT     104 validation_id, 2 scenario_id
        UNION SELECT     100 validation_id, 3 scenario_id
        UNION SELECT     101 validation_id, 3 scenario_id
        UNION SELECT     102 validation_id, 3 scenario_id) A

        ) B

    GROUP BY scenario_id
) C

GROUP BY validation_ids

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You can use two levels of aggregation:

select validation_ids, group_concat(scenario_id)
from (select scenario_id,
             group_concat(validation_id order by validation_id) as validation_ids
      from validations v
      group by scenario_id
     ) v
group by validation_ids
having count(*) >= 2;

Upvotes: 2

Related Questions