For example I have this statement:
my name is Joseph and my father's name is Brian
This statement is splitted by word, like this table:
------------------------------ | ID | word | ------------------------------ | 1 | my | | 2 | name | | 3 | is | | 4 | Joseph | | 5 | and | | 6 | my | | 7 | father's | | 8 | name | | 9 | is | | 10 | Brian | ------------------------------
I want to get previous and next word of each word
For example I want to get previous and next word of “name”:
-------------------------- | my | name | is | -------------------------- | father's | name | is | --------------------------
How could I get this result?
Advertisement
Answer
you didn’t 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