MySQL SELECT WHERE IN Incrementing Value

Question

I have a DB, which holds words with position identifier. For instance the following string:

The Quick Brown Fox Jumps Over The Lazy Dog

The position identifiers would be:

1. The
2. Quick
3. Brown
4. Fox
5. Jumps
6. Over
7. The
8. Lazy
9. Dog

What I would like to accomplish is that I select a string where Fox is followed directly by Jumps. I use the following MySQL syntax:

SELECT PositionIdentifier 
FROM TableName 
WHERE Word='Fox' 
   OR PositionIdentifier + 1 IN 
       (SELECT PositionIdentifier 
        FORM TableName 
        WHERE Word='Jumps'
       )

However, the plus one statement is not working as expected.

How could I achieve this?

Update:

Oke, to clearify. This is my table:

StringID    Word     Counter     Positions

1           Fox      2           6|35

1           Jumps    4           7|12|36|46

1           Over     3           8|37|41

So, from this tabel I would like to retrieve the Positions where Over follows Jumps which follows Fox. So in this example, I would like to retrieve 6,7 and 8 as a pair and 35, 36 and 37 as a pair of positions

I hope this is more clear. Thx

Answer 1

Give it a try like this

SELECT PositionIdentifier 
FROM TableName 
WHERE Word='Fox' 
   AND PositionIdentifier IN
       (SELECT (PositionIdentifier -1 ) AS PrevPosID
        FROM TableName 
        WHERE Word='Jumps'
       )

assuming that your PositionIdentifier column is an INT otherwhise you might have to CAST the substraction result