10

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!

Hugo Demiglio
  • 1,599
  • 1
  • 16
  • 25

1 Answers1

16

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
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
Lieven Keersmaekers
  • 57,207
  • 13
  • 112
  • 146
  • 1
    Of course you mean `ORDER BY ABS(id - @id)` where `@id` is the specific id (`9` in the example) – ypercubeᵀᴹ Apr 11 '12 at 07:38
  • @ypercube - thx, you are right offcourse. *I used the actual value because I believe the `@` is SQL Server syntax. Do correct me if I'm wrong. – Lieven Keersmaekers Apr 11 '12 at 07:42
  • OP is not clear on what he means with "the IDs around an ID" so this is one of the two possible senarios. For ids being: `4,5,6,7,8,9,23,47`, this query will show `5,6,7,8,9` which is fine (since OP's intentions are unclear). The other senario would be to show two ids from the left and two from the right: `7,8,9,23,47` – ypercubeᵀᴹ Apr 11 '12 at 07:48
  • 1
    Regarding variables, it depends on how he is passing them (PHP, stored procedures, etc). A nice example with the different types of variables on MySQL: [MySQL: @variable vs. variable. Whats the difference?](http://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference) – ypercubeᵀᴹ Apr 11 '12 at 07:51
  • To get interval values, I added the clause `WHERE ABS(ID - 9) BETWEEN -3 AND 3` and remove `LIMIT 5` – Hugo Demiglio Apr 11 '12 at 07:56
  • @ypercube - I seem to be needing a lot of help today – Lieven Keersmaekers Apr 11 '12 at 08:30
  • No, your answer was 100% correct. I just added a performance improvement :) – ypercubeᵀᴹ Apr 11 '12 at 08:33
  • 1
    Both of these queries produce a syntax error on MariaDB 10.3.23. The first one at the `*` sign, the second at the `UNION` keyword. – Tejes Aug 05 '20 at 09:21