CODEWITHSUNDEEP
mysqlsql
Hugo Demiglio
Hugo Demiglio

Reputation: 1589

Select before and after rows around id with mysql

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!

Upvotes: 10

Views: 9510

Answers (1)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

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

Upvotes: 16

Related Questions