Let's learn Lag Function in Sql Server 2012

Introduction

Sql Server 2012 has brought a lot of new functions for the T-Sql developers. In this article we will look into 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

A case study

Let us first create the environment

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 the 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 as bolstered in the below figure

As specified earlier, whatever example follows for Lead will be applicable for Lag. Henceforth, the scenarios are not repeated here. Please take at look into the Lead function, apply those scenarios with Lag and you will come to know about it.

Conclusion

SQL Server 2012 (Denali) seems to be very mature and promising and has embedded with many new functions.In this article we have looked into the Lag function and it's various usage.Hope the article will be useful.

Thanks for reading