Postgres Next/Previous row SQL Query

Question

I have the following table structures in a Postgres 9.1 database but the ideal solution should be DB agnostic if possible:

Table: users
|id|username|
|1 |one     |
|2 |two     |
|3 |three   |

Table: items
|id|userid|itemname|created  |
|1 |1     |a       |timestamp|
|2 |1     |b       |timestamp|
|3 |1     |c       |timestamp|
|4 |2     |d       |timestamp|
|5 |2     |e       |timestamp|
|6 |2     |f       |timestamp|
|7 |3     |g       |timestamp|
|8 |3     |h       |timestamp|
|9 |3     |i       |timestamp|

I have a query (for a view) which provides the next and previous item.id.

e.g.

View: UserItems
|id|userid|itemname|nextitemid|previtemid|created  |
|1 |1     |a       |2         |null      |timestamp|
|2 |1     |b       |3         |1         |timestamp|
|3 |1     |c       |4         |2         |timestamp|
|4 |2     |d       |5         |3         |timestamp|
|5 |2     |e       |6         |4         |timestamp|
|6 |2     |f       |7         |5         |timestamp|
|7 |3     |g       |8         |6         |timestamp|
|8 |3     |h       |9         |7         |timestamp|
|9 |3     |i       |null      |8         |timestamp|

I can do this with the following query:

SELECT
  DISTINCT i.id AS id,
  i.userid AS userid,
  i.itemname AS itemname,
  LEAD(i.id) OVER (ORDER BY i.created DESC) AS nextitemid,
  LAG(i.id) OVER (ORDER BY i.created DESC) AS previtemid,
  i.created AS created
FROM items i
  LEFT JOIN users u
  ON i.userid = u.id
ORDER BY i.created DESC;

Can you help to solve the following problems:

1) Is there a way to make the ids wrap i.e.

• the NULL itemid in the last row of the nextitemid column should be 1
• the NULL itemid in the first row of the previtemid column should be 9

2) is there a performant way to group the next and previous itemids by userid e.g.

NB: in this example the itemids for a user are sequential, this is not the case for real data, the itemids for each user are interleaved.

View: UserItems
|id|userid|itemname|nextitemid|previtemid|nextuseritemid|prevuseritemid|created  |
|1 |1     |a       |2         |9         |2             |3             |timestamp|
|2 |1     |b       |3         |1         |3             |1             |timestamp|
|3 |1     |c       |4         |2         |1             |2             |timestamp|
|4 |2     |d       |5         |3         |5             |6             |timestamp|
|5 |2     |e       |6         |4         |6             |4             |timestamp|
|6 |2     |f       |7         |5         |4             |5             |timestamp|
|7 |3     |g       |8         |6         |8             |9             |timestamp|
|8 |3     |h       |9         |7         |9             |7             |timestamp|
|9 |3     |i       |1         |8         |7             |8             |timestamp|

Bak

Answer 1

Q1: FIRST_VALUE/LAST_VALUE

Q2: PARTITION BY (as Roman Pekar already suggested)

SEE FIDDLE HERE

SELECT
  DISTINCT i.id AS id,
  i.userid AS userid,
  i.itemname AS itemname,
  COALESCE(LEAD(i.id)        OVER (ORDER BY i.created DESC)
          ,FIRST_VALUE(i.id) OVER (ORDER BY i.created DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS nextitemid,
  COALESCE(LAG(i.id)         OVER (ORDER BY i.created DESC)
          ,LAST_VALUE(i.id)  OVER (ORDER BY i.created DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS previtemid,
  COALESCE(LEAD(i.id)        OVER (PARTITION BY i.userid ORDER BY i.created DESC)
          ,FIRST_VALUE(i.id) OVER (PARTITION BY i.userid ORDER BY i.created DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS nextuseritemid,
  COALESCE(LAG(i.id)         OVER (PARTITION BY i.userid ORDER BY i.created DESC)
          ,LAST_VALUE(i.id)  OVER (PARTITION BY i.userid ORDER BY i.created DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS prevuseritemid,
  i.created AS created
FROM items i
  LEFT JOIN users u
  ON i.userid = u.id
ORDER BY i.created DESC;

Irritate

That does the trick nicely, thanks very much guys. You can remove the untested comment too sqlfiddle.com/#!1/58d92/28 – Bak

Answer 2

update I've forgot about first_value and last_value functions in PostgreSQL, thanks to dnoeth he reminded me about it. However, his query is not working, because last_value is working with default window RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and will not return correct results, so you either have to change range inside the over clause or use first_value with order by asc:

select
    i.id as id,
    i.userid as userid,
    i.itemname as itemname,
    coalesce(
        lead(i.id) over(order by i.created desc),
        first_value(i.id) over(order by i.created desc)
    ) as nextitemid,
    coalesce(
        lag(i.id) over(order by i.created desc),
        first_value(i.id) over(order by i.created asc)
    ) as previtemid,
    coalesce(
        lead(i.id) over(partition by i.userid order by i.created desc),
        first_value(i.id) over(partition by i.userid order by i.created desc)
    ) as nextuseritemid,
    coalesce(
        lag(i.id) over(partition by i.userid order by i.created desc),
        first_value(i.id) over(partition by i.userid order by i.created asc)
    ) as prevuseritemid,
    i.created as created
from items as i
   left outer join users as u on u.id = i.userid
order by i.created desc

sql fiddle demo

previous version
I think you could do this:

SELECT
  i.id AS id,
  i.userid AS userid,
  i.itemname AS itemname,
  coalesce(
      LEAD(i.id) OVER (ORDER BY i.created DESC),
      (select t.id from items as t order by t.created desc limit 1)
  ) AS nextitemid,
  coalesce(
      LAG(i.id) OVER (ORDER BY i.created DESC),
      (select t.id from items as t order by t.created asc limit 1)
  ) AS previtemid,
  coalesce(
      LEAD(i.id) OVER (partition by i.userid ORDER BY i.created DESC),
      (select t.id from items as t where t.userid = i.userid order by t.created desc limit 1)
  ) AS nextuseritemid,
  coalesce(
      LAG(i.id) OVER (partition by i.userid ORDER BY i.created DESC),
      (select t.id from items as t where t.userid = i.userid order by t.created asc limit 1)
  ) AS prevuseritemid,
  i.created AS created
FROM items i
  LEFT JOIN users u
  ON i.userid = u.id
ORDER BY i.created DESC;

sql fiddle demo

Francinefrancis

Yep, i forgot the ROWS. But instead of changing the sort order i would change the ROWS to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. This should allow the optimizer to do all the OLAP functions with the same PARTITION BY and ORDER BY in a single step – Irritate

yep, do it and I'll upvote you :) I wanted to upvote it, but it doesn't work, so you have to edit it first :) – Francinefrancis

Okay, i modified my answer :-) – Irritate