CODEWITHSUNDEEP
sqlpostgresql
v0ld3m0rt
v0ld3m0rt

Reputation: 904

PostgreSQL - select next and previous ids in a table

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.

Upvotes: 0

Views: 184

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

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
              );

Upvotes: 0

FatFreddy
FatFreddy

Reputation: 1210

an 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.

Upvotes: 1

Ryan Nghiem
Ryan Nghiem

Reputation: 2438

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;

Upvotes: 1

Related Questions