Oracle SQL distinct count without outputting columns that make it distinct -

i have table want output 1 field can part of queries where statement (where sid in (this new query)).

however because of can include sid in select, removing needed make distinct count work.

so select * tablea gives me:

sid     deta    detb 22222   8159    3763 22222   8159    3763 44444   4739    6135 44444   4739    6135 44444   4739    6134 44444   4739    6135 55555   5937    0223 55555   5936    0223 66666   8577    9497 66666   8577    9497 66666   8577    9497 66666   8576    9496 66666   8577    9497 88888   3595    0919 88888   3595    0919 88888   3595    0919 88888   3595    0914 77777   5678    3456 

then select distinct sid, deta, detb tablea gives me:

sid     deta    detb 22222   8159    3763 44444   4739    6134 44444   4739    6135 55555   5936    0223 55555   5937    0223 66666   8576    9496 66666   8577    9497 88888   3595    0914 88888   3595    0919 77777   5678    3456 

the data want this:

sid     deta    detb 44444   4739    6134 44444   4739    6135 55555   5936    0223 55555   5937    0223 66666   8576    9496 66666   8577    9497 88888   3595    0914 88888   3595    0919 

which can done using count of distinct, final output want this:

sid      44444   55555   66666    88888    

but cant achieve when outputting 1 field.

use group by:

select sid tablea group sid having count(distinct deta || ':' || detab) > 1; 

if wanted full rows (instead of sid values), use window functions:

select a.* (select a.*, count(distinct deta || ':' || detab) on (partition sid) cnt       tablea      ) cnt > 1;