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 aggregated:

 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 request (for presentation) that provides the next and previous item.id.

eg.

 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; 

Will you help solve the following problems:

1) Is there a way to make ids wrap ie

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

2) is there a capable way to group the next and previous elements using userid, for example.

NB: in this example, the elements for the user are sequential, this does not apply to real data, the elements for each user alternate.

 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 |

Answer 1

Q1: FIRST_VALUE / LAST_VALUE

Q2: PARTITION BY (as Roman Pekar already suggested)

WATCH FIVE 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; 

Answer 2

update I forgot about the functions first_value and last_value in PostgreSQL, thanks to dnoeth it reminded me of that. However, his request does not work, because last_value works with the default window RANGE BETWEEN UNLIMITED last_value AND CURRENT HAND and will not return the correct results, so you either have to change the 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