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

Question

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?

What database are you using? What version of that database are you using? SQL is a language but almost every database has a slightly different dialect of SQL that it supports. This sort of thing is much easier when you're using a database that supports analytic functions like `lead` and `lag`. — Justin Cave, Nov 23 '14 at 07:03
i use SQL 2012, support LAG and LEAD, but i want take result for 5 million word quickly, its important to take result very fast in my program — mahdi yousefi, Nov 24 '14 at 04:54

score 84 · Accepted Answer · 2014-11-23T09:59:27.393

84

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

edited Nov 23 '14 at 09:59

answered Nov 23 '14 at 09:53

with this code, in inner select statement the whole rows of table are combine and in outer: select rows with same word, if i want take only one word all of table words are combined and then select one of them... – mahdi yousefi Nov 24 '14 at 05:04
its ok, but i have over 100 milion records, and time to take result is very important, can i improve performance of this code – mahdi yousefi Nov 24 '14 at 05:11
@mahdiyousefi: please create a new question for that. – Nov 24 '14 at 09:20

score 31 · Answer 2 · answered Aug 16 '17 at 10:23

31

Why did no-body give the simple answer?

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

answered Aug 16 '17 at 10:23

Anonymous

1,015
1
10
14

6

You're failing to restrict results to next/previous of a particular word. The accepted answer has a `WHERE` clause. – Gili Jan 07 '18 at 08:02

Fermat's Little Student · Answer 3 · 2016-07-14T14:14:20.207

12

Without 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

edited Jul 14 '16 at 14:14

answered Nov 23 '14 at 07:52

Fermat's Little Student

5,549
7
49
70

2

this will not work if there are gaps in the id sequence – t-clausen.dk Nov 24 '14 at 10:46
@t-clausen.dk op said the table was created from splitting a sentence, so there shoudn't be any gap – Fermat's Little Student Nov 24 '14 at 16:31
additional column ROW_NUMBER() OVER (ORDER BY id) will do the trick then – madan May 14 '21 at 12:52

score 4 · Answer 4 · answered Nov 26 '14 at 03:34

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

Pரதீப் · Answer 5 · 2014-11-23T07:27:23.423

2

Try this

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`

edited Nov 23 '14 at 07:27

answered Nov 23 '14 at 07:01

Pரதீப்

91,748
19
131
172

Its ok, but i have more than 100 milion records, and time to take result is very important, can i improve performance of code? – mahdi yousefi Nov 24 '14 at 05:21

mlinth · Answer 6 · 2014-11-24T10:36:37.053

Here's a different approach, if you want the selects to be fast. It takes a bit of preparation work.

Create a new column (e.g. "phrase") in the database that will contain the words you want. (i.e. the previous, the current and next).
Write a trigger that on insert appends the new word to the previous row's phrase and prepends the previous row's word to the new row's word and fills phrase.
If the individual words can change, you'll need a trigger on update to keep the phrase in sync.

Then just select the phrase. You get much better speed, but at the cost of extra storage and slower insert and harder maintainability. Obviously you have to update the phrase column for the existing records, but you have the SQL to do that in the other answers.

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

6 Answers6

Linked

Related