title: Postgres Next/Previous row SQL Query
body: <p>I have the following table structures in a Postgres 9.1 database but the ideal solution should be DB agnostic if possible:</p>
  
  <pre>
  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|
  </pre>
  
  <p>I have a query (for a view) which provides the next and previous item.id.</p>
  
  <p>e.g.</p>
  
  <pre>
  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|
  </pre>
  
  <p>I can do this with the following query:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Can you help to solve the following problems:</p>
  
  <p>1) Is there a way to make the ids wrap i.e.</p>
  
  <ul>
  <li>the NULL itemid in the last row of the nextitemid column should be 1</li>
  <li>the NULL itemid in the first row of the previtemid column should be 9</li>
  </ul>
  
  <p>2) is there a performant way to group the next and previous itemids by userid e.g.</p>
  
  <p><i><b>NB:</b> 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.</i></p>
  
  <pre>
  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|
  </pre>