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

Question

I have a table like this

For example, I have this statement:

my name is joseph and my father name is brian 

This statement is broken into a 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 the previous and next meaning of the same word

For example, I want to get the previous and next word "name":

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

How can i do this?

Answer 1

you did not specify your DBMS, so the following is ANSI SQL:

 select prev_word, word, next_word from ( select id, lag(word) over (order by id) as prev_word, word, lead(word) over (order by id) as next_word from words ) as t where word = 'name'; 

SQLFiddle: http://sqlfiddle.com/#!12/7639e/1

Answer 2

No subqueries:

 SELECT a.word FROM my_table AS a JOIN my_table AS b ON b.word = 'name' AND abs(a.id - b.id) <= 1 ORDER BY a.id 

Answer 3

try it

 SELECT * FROM tablename a WHERE ID IN(SELECT ID - 1 FROM tablename WHERE word = 'name') -- will fetch previous rows of word `name` OR ID IN(SELECT ID + 1 FROM tablename WHERE word = 'name') -- will fetch next rows of word `name` OR word = 'name' -- to fetch the rows where word = `name` 

Answer 4

A different approach is used here if you want the choice to be quick. It takes a bit of preparation.

• Create a new column (for example, "phrase") in the database that will contain the words you want. (i.e. previous, current and next).

• Write a trigger that on the insert adds a new word to the previous line and adds the previous word of the line to the new word of the line and fills the Phrase.

• If individual words may change, you will need an update trigger to sync the phrase.

Then just select a phrase. You get much better speed, but at the expense of additional storage and a slower insertion and more convenient maintainability. Obviously you need to update the phrase column for existing records, but you have SQL to do this in other answers.

Answer 5

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:

Fiddler Demo

Answer 6

Why has no one given a simple answer?

 SELECT LAG(word) OVER ( ORDER BY ID ) AS PreviousWord , word , LEAD(word) OVER ( ORDER BY ID ) AS NextWord FROM words; 

Answer 7

I create an index in a column of words and set this code to quickly get the result:

 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