[SOLVED] How do I return a row with its next and previous rows?

How do I return a row with its next and previous rows?

I was wondering if it's possible to select a row with its next and previous rows if they exist. If the previous rows don't exist then the current row and the next rows should be returned or if the next rows don't exist then the previous and the current row should be returned. E.g. for a table having an id column and a text column called Words which are sorted alphabetically like (id ommitted):

Words

apple

banana

cat

dog

A query on the word cat should return banana,cat and dog, a query on the word apple should return apple and banana since there's no previous word and a on the word dog should return cat and dog since there's no next word after dog. I think the alphabetical ordering of the Word column should make this possible

This answer https://stackoverflow.com/a/17828285/16039676 gave something similar but it's not what I'm looking for

Edit: my recent current query shows me this error, how do i resolve this? Query error

My query

@Query("SELECT LAG(entry_word) OVER(ORDER BY entry_word) AS previous_word, entry_word AS current_word, LEAD(entry_word) OVER(ORDER BY entry_word) AS next_word FROM entry_words WHERE entry_word = :word")
List<String> getEntryWords(String word);

Solution

You need window functions - lag (previous item) and lead (next item)

https://www.sqlite.org/windowfunctions.html

Your window function should order by some ID that can identify previous and next item or if the order is alphabetical something like:

SELECT 
lag(words) OVER ( ORDER BY words) as prev,
words as curr,
lead(words) OVER ( ORDER BY words) as next
FROM ...