I have a simple question, suppose we have a table:
id A B
1 Jon Doe
2 Foo Bar
3 Jon Doe
4 Foo Bar
5 Jon Doe
6 Foo Bar
7 Jon Doe
8 Foo Bar
9 Jon Doe
10 Foo Bar
Is there a way to get next and previous 2 ids by passing an id. If I pass 5 as an Id then the query should return 3,4,6,7 as a result.
You can try:
x := 5;
SELECT *
FROM
tbl_name
Where id < x
order by id desc
limit 2
UNION
SELECT *
FROM
tbl_name
Where id > x
order by id asc
limit 2;
order by id. I editedan approach with window functions, using lag and lead:
select
lag(id,2) over (order by id) before2,
lag(id,1) over (order by id) before1,
id,
LEAD(id,1) over (order by id) next1,
LEAD(id,2) over (order by id) next2
from mytable order by id
you have to define, what should happen, selecting the upper or lower boundaries.
One method uses in:
select t.*
from t
where t.id in (select t2.id
from t t2
where t2.id < 5
order by t2.id desc
limit 2
) or
t.id in (select t2.id
from t t2
where t2.id > 5
order by t2.id asc
limit 2
);
