Finding duplicates from MySql

Question

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.

One way of thinking about it is to find all instances where there are null differences between datasets. — Strawberry
– Strawberry, Commented Mar 5, 2020 at 11:30

Gordon Linoff · Accepted Answer · 2020-03-05 11:34:53Z

2

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;

edited Mar 5, 2020 at 11:34

answered Mar 5, 2020 at 11:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

4 Comments

Sankar Over a year ago

what is 't' in the above sub query?

Gordon Linoff Over a year ago

@Strawberry . . . .Why? The OP wants different ids that have the same validations and specifically does not want scenario_id = 2, because it has no duplicates.

Sankar Over a year ago

Is there any other solution, if there is a column of type varchar(1000) in place of scenario_id and need to find the duplicates with same logic..

Gordon Linoff Over a year ago

@sandkar. . . (1) You can use group_concat() but you need to increase the group_concat_max_len environment parameter. (2) That would be a different question, which you can ask as a new question.

AntoineFe · Accepted Answer · 2020-03-05 12:08:58Z

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

Collectives™ on Stack Overflow

Finding duplicates from MySql

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related