SQL Server 2012: Introduced LAG function, the best alternative of Self Join to compare Previous Row

In this post, I am going to demonstrate one of new new LAG() function which is introduced by SQL Server 2012.

Generally, we are using Self Join to perform row level analysis by comparing Next and Previous rows.
SQL Server 2012 has a LAG() function is used for accessing data from the Previous Row in the same result set.

For example, we have a different transaction date in our table and require to find the total number of days difference between current and previous transaction date.
We can use LAG() function to achieve this.

Below is a full demonstration of this.

The Syntax:

The default OFFSET value is 1 means considering next immediate row and we can also change this OFFSET value. e.g. set OFFSET value of 4 means it accesses immediate fourth row.

First, Create sample table and data:

Calculate day difference using LAG():

The Result:

SQL Server LAG Function

Anvesh Patel

Leave a Reply

1 Comment on "SQL Server 2012: Introduced LAG function, the best alternative of Self Join to compare Previous Row"

Notify of
avatar
Sort by:   newest | oldest | most voted
Elmer
Guest

Saved being a favorite, I love your site!

wpDiscuz