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
No comments:
Post a Comment