How to get next and previous value?

Question

MySQL

Greetings,

Let's imagine that there is a primitive table with one column that stores arbitrary numerical values. Is there some way built into mysql to get N next M previous values ​​given a certain value?

For example, the table stores the values ​​- 2, 6, 10, 15, 1, 14
We have the value 10. How to get the previous (6) and next (15) values?

There are no other fields on the basis of which it would be possible to implement logic in the table (there is no autoincrement). The numbers are arbitrary and it is impossible to calculate "neighbors" using any formula. The option to get all records, for example, into an array will not work.

Answer 1

You need to store the offset in a separate column. Knowing the offset of the current line, then jump using LIMIT and OFFSET .
There are also cursors , but there you can only move in one direction.
Most likely, relational databases are not suitable for your task.

Answer 2

There are no other fields on the basis of which it would be possible to implement logic in the table (there is no autoincrement)

Then the problem is not solved, because it is impossible to define the previous and next ones without ORDER BY (and judging by "2, 6, 10, 15, 1, 14" it is impossible to do it by the main ORDER BY field).
If you can somehow sort (for example, by the main field value), then the following is like this:

SELECT value FROM table WHERE value > 10 ORDER BY VALUE LIMIT 1

Answer 3

https://dev.mysql.com/doc/refman/8.0/en/window-fun...
well, or in a highly artificial description:

select val where val<10 order by val limit N
union all
select val where val val>10 order by val limit M