# SQL Puzzle

Given a table with (for example) 3 columns

• section [String]
• SeqNum [Integer]
• txtValue [String]

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

It does not need be done in a single SQL Query

```select txtvalue from thetable group by txtvalue ,seqnum having count(1) > 1;```

That gets cases where the sequence number is different.
But the sequence order?
abc is different too.

You could normalise the sequence numbers by subtracting the lowest by group, I guess.
But I have a horrible feeling you want

A,1,abc
A,2,xyz
A,3,mno
B,32,abc
B,87,mno

to return no rows

No… because the Seqnums will NOT be the same… its the ORDER of the Seqnum not the VALUE

your query would indicate EVERYTHING was in error, which is not the case

…yeah, that…
Even ranking wont do it because there is zzz in the middle.
Hmm

Hence “puzzle”

Are intersections available to you?

SQLite

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)

Did this work out for you?

Sorry… I couldn’t figure out what it was attempting to do… I’m taking a more brute force approach since this is just a one-off execution… but thanks

That’s a shame, I put a lot of time into that.

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``````