The LAG() function in SQL Server is used to access data from a previous row within the same result set. It provides a way to look back at the previous row’s value and compare it with the current row’s value. Compare with Lead function, The LEAD function is used to retrieve the value from the next row in a query result set.
Syntax
LAG (scalar_expression [,offset] [,default_value])
OVER ([PARTITION BY partition_expression, ... ]
ORDER BY order_expression [ASC | DESC], ... )
scalar_expression: The expression to be returned from the previous row.offset: An optional argument that specifies the number of rows to move back from the current row. The default value is 1.default_value: An optional argument that specifies a default value to return if the offset is beyond the first row.
Example 1
SELECT ProductName, Sales,
LAG(Sales) OVER (ORDER BY Sales) AS PrevSales
FROM SalesTable
In this example, the query returns the ProductName, Sales, and the previous row’s Sales value. The LAG() function is used to access the previous row’s value and compare it with the current row’s value. The result set is ordered by Sales.
Example 2
SELECT EmployeeName, Salary,
LAG(Salary, 2, 0) OVER (ORDER BY Salary) AS Prev2ndSalary
FROM EmployeeTable
In this example, the query returns the EmployeeName, Salary, and the second previous row’s Salary value. The LAG() function is used to access the second previous row’s value and compare it with the current row’s value. The result set is ordered by Salary, and the offset argument is set to 2, which specifies that the function should move two rows back from the current row. The default_value argument is set to 0, which specifies a default value to return if the offset is beyond the first row.