How to use the LAG() Function in SQL?

Programming Language
#1

At many instances, the user would like to access data of the previous row or any row before the previous row from the current row.

To solve this problem SQL Server’s LAG() window function can be used.

LAG() : SQL Server provides LAG() function which is very useful in case the current row values need to be compared with the data/value of the previous record or any record before the previous record. The previous value can be returned on the same record without the use of self join making it straightforward to compare.

Syntax :

LAG (scalar_expression [, offset] [, default]) 
OVER ( [ partition_by ] order_by )

Where :

  1. scalar_expression – The value to be returned based on the specified offset.
  2. offset – The number of rows back from the current row from which to obtain a value. If not specified, the default is 1.
  3. default – default is the value to be returned if offset goes beyond the scope of the partition. If a default value is not specified, NULL is returned.
  4. over ( [ partition_by] order_by) – partition_by divides the result set produced by the FROM clause into partitions to which the function is applied. If you omit PARTITION BY clause, the function treats whole result set as a single group. By default order_by clause sorts in ascending order.