LAG
Description
Accesses data from a previous row in the same result set without the use of a self-join starting with SQL Server 2012 (11.x). LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row. (COUNT in Transact-SQL).
Sample Source Pattern
Syntax
LAG (scalar_expression [,offset] [,default])
OVER ( [ partition_by_clause ] order_by_clause )
Examples
Code:
SELECT TOP 10
LAG(E.VacationHours,1) OVER(ORDER BY E.NationalIdNumber) as PREVIOUS,
E.VacationHours AS ACTUAL
FROM HumanResources.Employee E
Result:
PREVIOUS|ACTUAL|
--------+------+
NULL| 10|
10| 89|
89| 10|
10| 48|
48| 0|
0| 95|
95| 55|
55| 67|
67| 84|
84| 85|
Last updated