Menu
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?
621
SQL Server LAG() is a window function that provides access to a row at a specified physical offset which comes before the current row. In other words, by using the LAG() function, from the current row, you can access data of the previous row, or the row before the previous row, and so on.
You can use UNION to get the previous and next record in MySQL. Insert some records in the table using insert command. Display all records from the table using select statement.
1) You can use MAX or MIN along with OVER clause and add extra condition to it. The extra condition is "ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING" which will fetch previous row value. Check this: SELECT *,MIN(JoiningDate) OVER (ORDER BY JoiningDate ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS EndDate.
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
173
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
