SQL Find a result 1 field below the search

Question

As part of a project to learn SQL I've downloaded the dictionary as a .csv and set up a table (1 column with 307,104 words in it called 'Words').

I'm stuck on a some queries, which are:

• What word comes after 'Database'
• What word comes 2 words after 'Database'
• What word come before 'Database'
• What word comes 2 words before 'Database'

I can't find the keyword relating to this type of query, any help would be appreciated.

Answer 1

You can enumerate the words with row_number() in a common table expression (if your database supports these features), then filter in the outer query:

with cte as (
    select word, row_number() over(order by word) rn
    from mytable 
)
select c.word
from cte c
where c.rn = (
    select c1.rn from cte c1 where c1.word = 'database'
) + 1

This gives you the word that immediately follows "database". You can change the final + 1 to get the other results (for example, to get the word two positions before, you would use - 2).

Another option uses a row-limiting query.

To get the next word:

select word
from mytable
where word > 'database'
order by word
limit 1

The word two positions after:

select word
from mytable
where word > 'database'
order by word
limit 1, 1

Preceding word:

select word
from mytable
where word < 'database'
order by word desc
limit 1

The word two positions before:

select word
from mytable
where word < 'database'
order by word desc
limit 1, 1

Note: the syntax of the row-limiting clause varies across databases.