oracle, find duplicate in one column based on another-CodePudding

create table stest1(x number,y number);

insert into stest1 values(1,10);
insert into stest1 values(1,11);
insert into stest1 values(1,10);

insert into stest1 values(2,9);
insert into stest1 values(2,9);
insert into stest1 values(1,9);

commit;

I would like to know all the values in column x that have more than one values of y

so from above, 1 has more than one value stored in y (10,11 and 9) where as 2 has only one value which is 9, so the result of my query should give 1

how to write such a query ?

CodePudding user response：

If all you are after is the x, we can self join like this:

select distinct s1.x
from stest1 s1
inner join stest1 s2
    on s1.x = s2.x
    and s1.y <> s2.y

EDIT:

To get both x and y, you could move the above into a CTE and then only select the existing x:

with dupes as
(
    select distinct s1.x
    from stest1 s1
    inner join stest1 s2
        on s1.x = s2.x
        and s1.y <> s2.y
)
select stest1.*
from stest1
where stest1.x in (select x from dupes)