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.

Answer

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

Advertisement

Answer