LAG (SQL)
A window function that assigns the value of the field column for the row that is offset rows before the given row within the specified window frame.
Synopsis
LAG(field[,offset[,default]])
Description
LAG assigns the value of the field column for the row that is specified by the offset. By default, LAG() will assign the value NULL if the given row does
not have a row offset rows before it within its
window frame.
The user has the option to assign an alternate value under these
conditions by including a value default.
Arguments
field
A column that specifies the value to
be assigned.
offset
This optional argument is an integer specifying from which row to take the value of the field column. If no offset is specified, the function assigns the value of the field column 1 row before the given row by default.
default
This optional argument specifies what value to return if the given row does not have a row offset rows before it within its window frame. If no default is specified, the value NULL is assigned.
Examples
The following example returns the previous salary for each employee within each department:
SELECT LAG(Salary) OVER (PARTITION BY Department ORDER BY Salary) FROM Company.Employee
The following example specifies an offset and default argument to calculate the salary of the previous employee within each department. If no such employee exists, the value 0 is returned:
SELECT LAG(Salary, 1, 0) OVER (PARTITION BY Department ORDER BY Salary) FROM Company.Employee
See Also
