I would like to do the following: 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;; 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 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? Thanks in advance Andrew Bailey