Lag Function

Purpose: This function returns result set starting from the previous row in the table.

Syntax: Lag(expression [,offset [,default] ] ) over( [ Partition_By_clause] order by clause)

Where,

Expression => A table column or built-in function but not analytical functions

Offset => It is optional and represents the physical offset from the current row in the table. If not specified, the default value is 1 and cannot accept negative numbers.

Default = > It is again optional. If not specified, then whenever the offset value goes out of the table bounds, then default null is returned.

Partition_By_clause = > Partition the query result set. It is again optional

Order By Clause = > Indicates how the data is ordered within the partition.

N.B.~Lag and Lead are the opposite side of a coin and henceforth whatever example we have seen for Lead will be applicable for Lag. Henceforth, it will not be wise to repeat the same scenarios here.We will see some of the example of Lag though

Before going to look into some examples of Lag we will first create the test environment

Run the below script for doing so

IF EXISTS (SELECT * FROM sys.objects WHERE name = N'MatchTable' AND type = 'U')
    DROP TABLE MatchTable
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE [dbo].[MatchTable](
	[MatchID] [int] IDENTITY(1,1) NOT NULL,
	[MatchGroup] [varchar](8) NULL,
	[MatchBetween] [varchar](50) NULL,
	[ScheduleDate] [date] NULL
) ON [PRIMARY]
GO

--Insert records
Insert Into MatchTable Values
('Group-A','India VS Australia','08/14/2011')
,('Group-A','India VS Pakistan','08/15/2011')
,('Group-A','India VS Newzealand','08/16/2011')
,('Group-A','Australia VS Pakistan','08/17/2011')
,('Group-A','Australia VS Newzealand','08/18/2011')
,('Group-A','Newzealand VS Pakistan','08/19/2011')
,('Group-B','USA VS WestIndies','08/20/2011')
,('Group-B','USA VS Ireland','08/21/2011')
,('Group-B','USA VS Bangaladesh','08/22/2011')
,('Group-B','WestIndies VS Ireland','08/23/2011')
,('Group-B','WestIndies VS Bangaladesh','08/24/2011')
,('Group-B','Ireland VS Bangaladesh','08/25/2011')

-- Project the records
Select * From MatchTable

/* Result
MatchID	MatchGroup	MatchBetween		ScheduleDate
1	Group-A		India VS Australia	2011-08-14
2	Group-A		India VS Pakistan	2011-08-15
3	Group-A		India VS Newzealand	2011-08-16
4	Group-A		Australia VS Pakistan	2011-08-17
5	Group-A		Australia VS Newzealand	2011-08-18
6	Group-A		Newzealand VS Pakistan	2011-08-19
7	Group-B		USA VS WestIndies	2011-08-20
8	Group-B		USA VS Ireland		2011-08-21
9	Group-B		USA VS Bangaladesh	2011-08-22
10	Group-B		WestIndies VS Ireland	2011-08-23
11	Group-B		WestIndies VS Bangaladesh2011-08-24
12	Group-B		Ireland VS Bangaladesh	2011-08-25
*/

Example: Find the previous match date and the previous match between the teams

Select 
	MatchID
	,MatchGroup
	,MatchBetween
	,ScheduleDate
	,PrevMatchBetween = Lag (MatchBetween) Over(Order by ScheduleDate)
	,PrevMatchDate = Lag (ScheduleDate) Over(Order by ScheduleDate)
From MatchTable 

--OR

Select 
	MatchID
	,MatchGroup
	,MatchBetween
	,ScheduleDate
	,PrevMatchBetween = Lag (MatchBetween,1) Over(Order by ScheduleDate)
	,PrevMatchDate = Lag (ScheduleDate,1) Over(Order by ScheduleDate)
From MatchTable 

/* Result

MatchID	MatchGroup	MatchBetween		ScheduleDate	PrevMatchBetween	PrevMatchDate
1	Group-A		India VS Australia	2011-08-14	NULL			NULL
2	Group-A		India VS Pakistan	2011-08-15	India VS Australia	2011-08-14
3	Group-A		India VS Newzealand	2011-08-16	India VS Pakistan	2011-08-15
4	Group-A		Australia VS Pakistan	2011-08-17	India VS Newzealand	2011-08-16
5	Group-A		Australia VS Newzealand	2011-08-18	Australia VS Pakistan	2011-08-17
6	Group-A		Newzealand VS Pakistan	2011-08-19	Australia VS Newzealand	2011-08-18
7	Group-B		USA VS WestIndies	2011-08-20	Newzealand VS Pakistan	2011-08-19
8	Group-B		USA VS Ireland		2011-08-21	USA VS WestIndies	2011-08-20
9	Group-B		USA VS Bangaladesh	2011-08-22	USA VS Ireland		2011-08-21
10	Group-B		WestIndies VS Ireland	2011-08-23	USA VS Bangaladesh	2011-08-22
11	Group-B		WestIndies VS Bangaladesh2011-08-24	WestIndies VS Ireland	2011-08-23
12	Group-B		Ireland VS Bangaladesh	2011-08-25	WestIndies VS Bangaladesh2011-08-24

*/

Now let us try to understand the behavior of the Lag function. We have not specified any offset in this query and hence the default value of 1 has been taken into granted. Henceforth, it ended at N-1 th row from the bottom. However, if we explicitly specify the offset value as 1, it will return the same result. This function takes into account TopRowNumber and BottomRowNumber(like Lead) and skips the number of rows from the bottom

[TopRowNumber1007] = Scalar Operator([RowNumber1006]-(1)), [BottomRowNumber1008] = Scalar Operator([RowNumber1006]-(1))

As specified earlier, whatever example follows for Lead will be applicable for Lag. Henceforth, the scenarios are not repeated here

Hope this helps