SQL: Getting the Previous and Next Records

Every so often it's necessary to compare a record with a previous or next record, based on some kind of ordering.  Many newer versions of databases support this with LAG and LEAD functions.  That seems like the ideal way to handle the problem,  but us lucky few we don't have that option so we have to do it in a round about sort of way.

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.