Find all occurances where txtValues are in a different seq order in two (or more) sections
Example
section “AAA” SeqNum 1 TxtValue “abc”
section “AAA” SeqNum 2 TxtValue “def”
section “AAA” SeqNum 3 TxtValue “ghi”
section “AAA” SeqNum 4 TxtValue “jkl”
section “BBB” SeqNum 14 TxtValue “abc”
section “BBB” SeqNum 17 TxtValue “ghi”
section “BBB” SeqNum 18 txtValue “zzz”
section “BBB” SeqNum 19 TxtValue “def”
section “BBB” SeqNum 20 TxtValue “jkl”
Here is would detect def and ghi because in Section AAA the order is def,ghi but in BBB it is ghi,def
the fact that zzz exists and is between them in Section BBB does not affect the relation between the two
Somehow it needs to compare the order of pairs of txtValues from one section to pairs of txtValues in every other Section
Here is some code that works. You have to run it for each distinct section, easy enough in a prepared statement.
begin;
create table example (
section text,
seqNum int,
txtValue text
);
create index example_section_idx on example (section);
create index example_seqNum_idx on example (seqNum);
create index example_txtValue_idx on example (txtValue);
insert into example (
section,
seqNum,
txtValue
) values
('AAA', 1, 'abc'),
('AAA', 2, 'def'),
('AAA', 3, 'ghi'),
('AAA', 4, 'jkl'),
('BBB', 14, 'abc'),
('BBB', 17, 'ghi'),
('BBB', 18, 'zzz'),
('BBB', 19, 'def'),
('BBB', 20, 'jkl')
;
select
*
from
example as main
where
section <> 'AAA'
and exists (
select txtValue
from example b
where
b.section = 'AAA'
and b.seqNum > (
select seqNum
from example c
where c.section = b.section and c.txtValue = main.txtValue
)
intersect
select txtValue
from example as d
where
d.section = main.section
and d.seqNum < main.seqNum
)
;
rollback;
Perhaps someone can see a way to do it in one shot.
Thanks… I will try that out later today.
My current project has a rather involved (albeit not to large) a dataset, that needs to have all the sections be in the same order.
Where some sections may have other items between (like zzz in the example)
Fyi, it compares the elements that come before the test record in its group to the ones that come after the corresponding element in group AAAA. If any of the same elements appear, the test record is out of sequence. You have to call it for each group to get all the results.
I appreciate the effort and the time… I really do…
here is what I came up with… like I said brute force,
First create a table of all the possible ordered pairs
A
B
C
D
would become
A B
A C
A D
B C
B D
C D
Create table .xyz AS
Select Distinct a.control_id,a.propcaption As theFirst,b.propcaption As theSecond
From .property a,property b
Where a.property_id < b.property_id
And a.propcaption <> b.propcaption
And a.control_id = b.control_id
And a.section_id = b.section_id"
Then select out those where reversed pairs exist
Select Distinct control_id, c.theFirst,c.theSecond
From xyz c,
(
Select Distinct a.thefirst,a.theSecond
From xyz a
Where Exists (Select 8
From xyz b
Where a.thefirst = b.thesecond
And a.thesecond = b.thefirst) ) b
Where ( c.thefirst = b.thefirst And c.thesecond = b.thesecond)
Or ( c.thesecond = b.thefirst And c.thefirst = b.thesecond)
Order By c.thefirst,c.thesecond