CODEWITHSUNDEEP
mysqlsql
R Ban
R Ban

Reputation: 97

duplicate records in table in SQL with two same column values and one different value

I am having three fields and I want to find out unique records with same c3 and c1 and different c2.

My c1, c2 and c3 are column names. I need to find out with in the same c1 and c3 where is c2 different.

eg.

+-----+------+--------+
| c1  |  c2  |   c3   |
+-----+------+--------+
| A10 | Z10  | text1  |
| A10 | Z10  | text1  |
| A10 | Z40  | text2  |
| A10 | Z50  | text1  |
| A20 | Z100 | text1  |
| A20 | Z200 | text1  |
| A50 | Z10  | text1  |
| A60 | Z10  | text1  |
| A60 | Z10  | text1  |
+-----+------+--------+

My output reqd is from A10 group as C3 is duplicated

so I need:

+-----+-----+-------+
| c1  | c2  |  c3   |
+-----+-----+-------+
| A10 | Z10 | text1 |
| A10 | Z50 | text1 |
+-----+-----+-------+

from A20 group both should appear as C3 is same and C2 is different.

+-----+------+-------+
| c1  |  c2  |  c3   |
+-----+------+-------+
| A20 | Z100 | text1 |
| A20 | Z200 | text1 |
+-----+------+-------+

and from A50 and A60 nothing should in the output as either they are unique or duplicated.

Final output is :

+-----+------+-------+
| c1  |  c2  |  c3   |
+-----+------+-------+
| A10 | Z10  | text1 |
| A10 | Z50  | text1 |
| A20 | Z100 | text1 |
| A20 | Z200 | text1 |
+-----+------+-------+

I tried to find out duplicate C3 and applied various joins but not able to get the required results.

Upvotes: 0

Views: 70

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I think the simplest way to do this uses exists:

select distinct c1, c2, c3
from t
where exists (select 1
              from t t2
              where t2.c1 = t.c1 and t2.c3 = t.c2 and
                    t2.c2 <> t.c2
             );

With an index on (c1, c3, c2), I would also expect this to have the best performance (although it would be interesting if a test showed that this is not the case).

Upvotes: 0

Nabav
Nabav

Reputation: 273

This should give you what you need:

with t as
(
    select
        c1,
        c3,
        count(distinct c2) as n
    from
        YourTable
    group by
        c1,
        c3
    having
        count(distinct c2) > 1
)

select distinct
    YourTable.c1,
    YourTable.c2,
    YourTable.c3
from
    YourTable
inner join
    t
on
    YourTable.c1 = t.c1
    and YourTable.c3 = t.c3

Upvotes: 3

Related Questions