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:

Expected result:

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?

CodePudding user response：

You can accomplish this with a CTE and then either inner join or an in subquery.

Inner Join

with valid as
(
    select distinct first_id
        , count(distinct second_id) over (partition by first_id) cnt
    from tbl
)
select tbl.*
from tbl
inner join valid v
    on tbl.first_id = v.first_id
    and v.cnt > 1

In Subquery

with valid as
(
    select distinct first_id
        , count(distinct second_id) over (partition by first_id) cnt
    from tbl
)
select tbl.*
from tbl
where tbl.first_id in (select first_id from valid where cnt > 1)

CodePudding user response：

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.