creating a pseudo linked list in sql

aggregate-functions, postgresql, sql

Solution

select 
  id, 
  location, 
  order_id,
  lag(id) over (order by order_id desc) as next_id
from your_table

Problem

I have a table that has the following columns ``` table: route columns: id, location, order_id ``` and it has values such as ``` id, location, order_id 1, London, 12 2, Amsterdam, 102 3, Berlin, 90 5, Paris, 19 ``` Is it possible to do a sql select statement in postgres that will return each row along with the id with the next highest order_id? So I want something like... ``` id, location, order_id, next_id 1, London, 12, 5 2, Amsterdam, 102, NULL 3, Berlin, 90, 2 5, Paris, 19, 3 ``` Thanks

Original source