Select only rows from sql table that have more than one different values for a second_id

Question

I have a big Oracle SQL table and I need to select only those rows that have more than one different values for the second_id

Current state:

first_id	second_id
1004	846
1004	846
1004	849
1005	324
1005	324

Expected result:

first_id	second_id
1004	846
1004	846
1004	849

As you can see, all entries with the first_id = 1005 were removed, as they all have the same second_id. As long as there are more than one different values for the second_id, it should keep the rows. Does anyone have any idea?

Answer 1

Analytic functions are good for this:

create table foo (first_id number, second_id number);

insert into foo values (1004,   846);
insert into foo values (1004,   846);
insert into foo values (1004,   849);
insert into foo values (1005,   324);
insert into foo values (1005,   324);
    
select first_id,second_id
from (
  select first_id,second_id, 
    count(distinct second_id) over (partition by first_id) cnt_distinct
  from foo)
where cnt_distinct > 1;

You can run the inner select to see what it returns for each row.