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;
- 5. Grouping and Windowing
- from Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012
- Publisher: Microsoft Press
- Released: December 2012
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.
Share this highlighthttp://www.safaribooksonline.com/a/training-kit-exam/61761/