Select before and after lines around id with mysql

Question

I need to get identifiers around an identifier with mySQL.

Example:

Identifiers in the table:

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

I need to know, for example, 5 identifiers around identifier 9. The request should return:

 4, 5, 9, 11, 15

Thanks!

+7

Hugo demiglio Apr 11 '12 at 7:25

source share

1 answer

Lieven keersmaekers · Accepted Answer · 2012-04-11T07:29:01+0000

Possible Solution:

calculate the absolute value of each identifier where you subtract your identifier.
Sort results and limit the result set to 5 reports.

SQL statement

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

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

If the goal is to get 2 id on the left and two identifiers on the right, the statement can be configured 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