O'Reilly logo
  • Dustin Waybright thinks this is interesting:

SELECT custid, orderid, orderdate, val,
  LAG(val)  OVER(PARTITION BY custid
                 ORDER BY orderdate, orderid) AS prev_val,
  LEAD(val) OVER(PARTITION BY custid
                 ORDER BY orderdate, orderid) AS next_val
FROM Sales.OrderValues;

From

Cover of Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012

Note

This query will return the previous and next order value by customer along with the current order information. The SELECT statement would need an ORDER BY clause in order to guarantee presentation ordering, but the window ordering will ensure that the LEAD and LAG values are based on the current row and partition.