Skip to main content

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

FeedbackOpens in a new tab