Get before and after rows of selected rows with specific condition

Question

my application get books and insert each word of this books to database, it may have more than 100 million words in books and inserted to database. Now I want get specific word with previous and next words. time to get result is very important.

for example : "The book words insert here in this table..."

------------------------------
|      ID      |    word     |
------------------------------
|       1      |     the     |
|       2      |     book    |
|       3      |     words   |
|       4      |     insert  |
|       5      |     here    |
|       6      |     in      |
|       7      |     this    |
|       8      |     table   |
|       .      |     .       |
|       .      |     .       |
|       .      |     .       |
------------------------------

or in other example:

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

in other related post friends write codes but this code take long time to get result, I want get the result table quickly:

related post: [question] Get previous and next row from rows selected with (WHERE) conditions

how often do you plan to run the query? – BI Dude Nov 25 '14 at 08:50 — BI Dude, Nov 25 '14 at 08:50

Saravana Kumar · Answer 1 · 2014-11-25T09:43:06.437

Use Join to get the expected result for SQL Server 2005 plus.

    create table words (id integer, word varchar(20));

    insert into words
    values
    (1 ,'my'),
    (2 ,'name'),
    (3 ,'is'),
    (4 ,'joseph'),
    (5 ,'and'),
    (6 ,'my'),
    (7 ,'father'),
    (8 ,'name'),
    (9 ,'is'),
    (10,'brian');

SELECT A.Id ,  C.word AS PrevName , 
               A.word AS CurName , 
               B.word AS NxtName 
FROM words AS A
LEFT JOIN words AS B ON A.Id = B.Id - 1
LEFT JOIN words AS C ON A.Id = C.Id + 1
WHERE A.Word = 'name'

Result:

enter image description here

Fiddler Demo

score 0 · Accepted Answer · answered Dec 03 '14 at 09:27

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

Get before and after rows of selected rows with specific condition

2 Answers2