Select the X "closest" ids?

Question

Let's say you have a table with a integer primary key named 'table_id'

Is it possible in a single query to extract the row with a specific id AND the X row before it AND the X row after it ?

For exemple, if your ids are (1,2,8,12,16,120,250,354), X is 2 and the id you have is 16, the select should return rows with ids 8,12,16,120,250

i know how to do it with several queries, I'm wondering about doing that in a single pass (sub queries, union and all are fine).

Thanks for any help

Answer 1

You can make a union between the items before and the item and the items after, but you have to make them subqueries to order them:

select * from (
  select * from thetable where table_id >= 16 order by table_id limit 3
) x
union all
select * from (
  select * from thetable where table_id < 16 order by table_id desc limit 2
) y
order by table_id

Answer 2

try this:

select table_id from table where id > 16 order by table_id desc limit 2
union all
select table_id from table where id <= 16 order by table_id asc limit 3;