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!

Accepted Answer

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
```

Select before and after rows around id with mysql

Solution

Problem

Related problems