mahdi yousefi mahdi yousefi - 4 months ago 6
SQL Question

Get previous and next row from rows selected with (WHERE) conditions

I have a table like that

For example I have this statement:

my name is joseph and my father name is brian


This statement is split by word like this table

------------------------------
| ID | word |
------------------------------
| 1 | my |
| 2 | name |
| 3 | is |
| 4 | joseph |
| 5 | and |
| 6 | my |
| 7 | father |
| 8 | name |
| 9 | is |
| 10 | brian |
------------------------------


I want to get previous and next value of same word

For example I want get previous and next word of "name":

--------------------------
| my | name | is |
--------------------------
| father | name | is |
--------------------------


How can I do this?

Answer

I create index on my words column and set this code to get result quickly:

WITH CTE AS 
(SELECT * FROM WordsTable WHERE word=N'Name')
SELECT          
    t2.word AS previousWord,
    t1.word,
    t3.word AS nextWord
FROM
    WordsTable AS t2,
    CTE AS t1,
    WordsTable AS t3
WHERE
    (t2.ID + 1)= t1.ID AND
    (t3.ID - 1) = t1.ID