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.
Advertisement
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