select id, row_number() over w as rownum, lag(id, 1) over w as prev, lead(id, 1) over w as next from route where id=1350 window w as (order by shortname, id asc rows between 1 preceding and 1 following) order by shortname, id ;
However this gives the result
1350;1;;
This does not work due to the id=1350 is always applied before the rows make it into the window therefore you only have rows which match id=1350, which is not what you want in this case.
The following query gives the result I am expecting
select * from (select id, row_number() over w as rownum, lag(id, 1) over w as prev, lead(id, 1) over w as next from route window w as (order by shortname, id rows between 1 preceding and 1 following) order by shortname, id) as s where id=1350
1350;3;1815;1813
This works because the id=1350 is not pushed down into the subquery which contain the windowing functions, this also means that the entire route table is processed and you may suffer from performance problems if the route table is, or gets big. You'll be able to confirm this by looking at the EXPLAIN output and noticing the lack of filter on the seqscan.
The explain plan is "Subquery Scan on s (cost=0.14..15.29 rows=1 width=32)" " Filter: (s.id = 1350)" " -> WindowAgg (cost=0.14..13.51 rows=143 width=12)" " -> Index Only Scan using route_idx on route (cost=0.14..10.29 rows=143 width=12)"
as it makes use of the index created as follows
CREATE INDEX route_idx ON route USING btree (shortname COLLATE pg_catalog."default", id);
I believe that the index has all the data that is needed to obtain the results in a single query.
Is it possible to write the query as a single select and if so how?
why not just write it as: select id, (select max(id) from route where id < 1350) as prev, (select min(id) from route where id > 1350) as next from route where id=2; ?
That should be much more efficient for a larger table as it should avoid the seqscan and allow the index to be used for all 3 numbers.
