SQL Puzzle

Databases
1

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

2

Top of my head:

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

3

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 :frowning:

4

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

5

Hence “puzzle” :slight_smile:

6

Are intersections available to you?

7

SQLite

8

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.

9

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)

10

Did this work out for you?

11

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

12

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.

13

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