oracle - PL/SQL - Compare sets of rows -

oracle - PL/SQL - Compare sets of rows -

sample data:

id value 1001 1001 b 1002 1002 1003 1003 b

standard of comparing id=1001

from here, want @ other ids != 1001... need create sure:

every other set of records grouped id has same quantity of associated rows. in example, every 1 passes test. for every value associated id=1001, exact match must found other ids. in example, 1002 fail because although has right number of rows, has 2 'a' values, , no 'b' value.

logically - seems simple enough... have been beating head against keyboard time now.

any wisdom or band-aids appreciated.

regards,

mike

here 1 way first query:

select s.id sample s s.id <> 101 grouping s.id having count(*) = (select count(*) sample s id = 101);

the sec similar. next comes close:

select s.id sample s total outer bring together (select s.* sample s s.id = 101 ) s100 on s.value = s100.vaue s.id <> 101 grouping s.id having count(*) = count(s.value) , count(s.value) = count(s100.value);

the problem query presence of duplicates. prepare that, need enumerate value each id:

select s.id (select s.*, row_number() on (partition s.id, s.value order s.id) seqnum sample s ) s total outer bring together (select s.*, row_number() on (partition s.id, s.value order s.id) seqnum sample s s.id = 101 ) s100 on s.value = s100.vaue , s.seqnum = s100.seqnum s.id <> 101 grouping s.id having count(*) = count(s.value) , count(s.value) = count(s100.value);

sql oracle