Let's assume we have this table:
CREATE TABLE EVENT_LOG ( system_name varchar(256), event_date datetime );
And let's say we want to know the previous and next entries for each record based on the system_name. Here is the query we could use:
WITH NEW_LOG AS ( select ROW_NUMBER() OVER (order by system_name, event_date) row_num , system_name, event_date FROM EVENT_LOG ) SELECT n.system_name, prev.event_date prev, n.event_date, nex.event_date next FROM NEW_LOG n LEFT JOIN NEW_LOG prev ON prev.system_name = n.system_name and prev.row_num = n.row_num -1 LEFT JOIN NEW_LOG nex ON nex.system_name = n.system_name and nex.row_num = n.row_num +1 order by n.system_name, n.event_date desc;
Or feel free to play around with this SQLFiddle.
Here is a good reference here as well: http://blog.sqlauthority.com/2013/09/22/sql-server-how-to-access-the-previous-row-and-next-row-value-in-select-statement/
Also, note I ran a very similar query as above on an older version of DB2 just fine.