Select before and after rows around id with mysql

Question

I need to get the IDs around an ID with mySQL.

Example:

IDs ​​in the table:

2, 4, 5, 9, 11, 15, 19, 22, 25

I need to know the 5 IDs around the ID 9, for example. The query should return:

4, 5, 9, 11, 15

Thanks!

Answer 1

A possible solution would be to

• calculate the absolute value of each id where you subtract your ID.
• order the results and limit the resultset to 5 records.

SQL Statement

SELECT ABS(ID - 9), *
FROM   MyTable
ORDER BY
       ABS(ID - 9)
LIMIT  5

Edit (thx to ypercube for pointing out a possible flaw in this solution)

If the intent is to get 2 id's from the left and two id's from the right, the statement can be adjusted as follows

SELECT * FROM MyTable WHERE ID <= 9 ORDER BY ID DESC LIMIT 3 
UNION ALL
SELECT * FROM MyTable WHERE ID > 9  ORDER BY ID ASC  LIMIT 2