In this article we will learn Lead function of Sql Server 2012 (Denali).
Introduction
Sql Server 2012 has brought a lot of new functions for the T-SQL developers. In this article we will look into Lead Function.
Purpose
This function returns result set starting from the next row in the table.
Syntax:
Lead(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.
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 1: Find next row result/Skip first row
Suppose we want to see the next match date and the next match between the teams. We can do this easily using the Lead function as under
Select
MatchID
,MatchGroup
,MatchBetween
,ScheduleDate
,NextMatchBetween = Lead (MatchBetween) Over(Order by ScheduleDate)
,NextMatchDate = Lead (ScheduleDate) Over(Order by ScheduleDate)
From MatchTable
--OR
Select
MatchID
,MatchGroup
,MatchBetween
,ScheduleDate
,NextMatchBetween = Lead (MatchBetween,1) Over(Order by ScheduleDate)
,NextMatchDate = Lead (ScheduleDate,1) Over(Order by ScheduleDate)
From MatchTable
Now let us try to understand the behavior of the Lead function. We have not specified any offset in this query and hence the default value of 1 has been taken into granted. Henceforth, it started from the second row . However, if we explicitly specify the offset value as 1, it will return the same result. This function takes into account TopRowNumber and BottomRowNumber and skips the number of rows from the top specified as bolstered in the below figure
Example 2:Find next to next row result/Skip first 2 rows
If we need to skip 2 rows, we need to specify 2 in the offset as under
Select
MatchID
,MatchGroup
,MatchBetween
,ScheduleDate
,NextMatchBetween = Lead (MatchBetween,2) Over(Order by ScheduleDate)
,NextMatchDate = Lead (ScheduleDate,2) Over(Order by ScheduleDate)
From MatchTable
We have skipped two rows as revealed from the above figure
Example 3:Specifying 0 or space in offset
If we give 0 in offset, indicates that we are not skipping any row
Select
MatchID
,MatchGroup
,MatchBetween
,ScheduleDate
,NextMatchBetween = Lead (MatchBetween,0) Over(Order by ScheduleDate)
,NextMatchDate = Lead (ScheduleDate,0) Over(Order by ScheduleDate)
From MatchTable
/*
MatchID MatchGroup MatchBetween ScheduleDate NextMatchBetween NextMatchDate
1 Group-A India VS Australia 2011-08-14 India VS Australia 2011-08-14
2 Group-A India VS Pakistan 2011-08-15 India VS Pakistan 2011-08-15
3 Group-A India VS Newzealand 2011-08-16 India VS Newzealand 2011-08-16
4 Group-A Australia VS Pakistan 2011-08-17 Australia VS Pakistan 2011-08-17
5 Group-A Australia VS Newzealand 2011-08-18 Australia VS Newzealand 2011-08-18
6 Group-A Newzealand VS Pakistan 2011-08-19 Newzealand VS Pakistan 2011-08-19
7 Group-B USA VS WestIndies 2011-08-20 USA VS WestIndies 2011-08-20
8 Group-B USA VS Ireland 2011-08-21 USA VS Ireland 2011-08-21
9 Group-B USA VS Bangaladesh 2011-08-22 USA VS Bangaladesh 2011-08-22
10 Group-B WestIndies VS Ireland 2011-08-23 WestIndies VS Ireland 2011-08-23
11 Group-B WestIndies VS Bangaladesh2011-08-24 WestIndies VS Bangaladesh2011-08-24
12 Group-B Ireland VS Bangaladesh 2011-08-25 Ireland VS Bangaladesh 2011-08-25
*/
Same thing can be achieved if we specify space as under
Select
MatchID
,MatchGroup
,MatchBetween
,ScheduleDate
,NextMatchBetween = Lead (MatchBetween,'') Over(Order by ScheduleDate)
,NextMatchDate = Lead (ScheduleDate,'') Over(Order by ScheduleDate)
From MatchTable
Example 4:Specifying negative offset
Lead function cannot accept negative offset
Select
MatchID
,MatchGroup
,MatchBetween
,ScheduleDate
,NextMatchBetween = Lead (MatchBetween,-1) Over(Order by ScheduleDate)
,NextMatchDate = Lead (ScheduleDate,-1) Over(Order by ScheduleDate)
From MatchTable
/*
Msg 8730, Level 16, State 1, Line 43
Offset parameter for Lag and Lead functions cannot be a negative value.
*/
Example 5:Specifying null value in offset yields null
Giving null value in offset yields null
Select
MatchID
,MatchGroup
,MatchBetween
,ScheduleDate
,NextMatchBetween = Lead (MatchBetween,null) Over(Order by ScheduleDate)
,NextMatchDate = Lead (ScheduleDate,null) Over(Order by ScheduleDate)
From MatchTable
Example 6:Working Lead function with fractional parts
We can even use fractions in the offset clause of Lead function.Let's see the below
Select
MatchID
,MatchGroup
,MatchBetween
,ScheduleDate
,NextMatchBetween = Lead (MatchBetween,12/10) Over(Order by ScheduleDate)
,NextMatchDate = Lead (ScheduleDate,12/10) Over(Order by ScheduleDate)
From MatchTable
Example 7: Lead demonstration with Calendar table
It can be use in conjunction with Common Table Expression as
;With DtCalender As(
Select Dt = Convert(Date,'8/1/2011')
Union All
Select DATEADD(dd,1,Dt)
From DtCalender
Where Dt < Convert(Date,'8/31/2011')
)
Select
MatchID
,MatchBetween
,Lead(c.Dt,1) Over(Order by c.Dt) As [Lead Example]
From MatchTable m
join DtCalender c on c.Dt = m.ScheduleDate
option (maxrecursion 0)
/*Result
MatchID MatchBetween Lead Example
1 India VS Australia 2011-08-15
2 India VS Pakistan 2011-08-16
3 India VS Newzealand 2011-08-17
4 Australia VS Pakistan 2011-08-18
5 Australia VS Newzealand 2011-08-19
6 Newzealand VS Pakistan 2011-08-20
7 USA VS WestIndies 2011-08-21
8 USA VS Ireland 2011-08-22
9 USA VS Bangaladesh 2011-08-23
10 WestIndies VS Ireland 2011-08-24
11 WestIndies VS Bangaladesh2011-08-25
12 Ireland VS Bangaladesh NULL
*/
The query is simple to understand. We have created a calendar table on the fly for one month (August, 2011) and that we are using inside the lead function only for those dates that matches with the match date column.
Example 8:Working with Scalar Expression or Function
We can even use a saclar expression or function in the expression field as
Select
MatchID
,MatchGroup
,MatchBetween
,ScheduleDate
,NextMatchBetween = Lead ((Select Top 1 MatchGroup from MatchTable),1) Over(Order by ScheduleDate)
,NextMatchDate = Lead (ScheduleDate,1) Over(Order by ScheduleDate)
From MatchTable
/* Result
MatchID MatchGroup MatchBetween ScheduleDate NextMatchBetween NextMatchDate
1 Group-A India VS Australia 2011-08-14 Group-A 2011-08-15
2 Group-A India VS Pakistan 2011-08-15 Group-A 2011-08-16
3 Group-A India VS Newzealand 2011-08-16 Group-A 2011-08-17
4 Group-A Australia VS Pakistan 2011-08-17 Group-A 2011-08-18
5 Group-A Australia VS Newzealand 2011-08-18 Group-A 2011-08-19
6 Group-A Newzealand VS Pakistan 2011-08-19 Group-A 2011-08-20
7 Group-B USA VS WestIndies 2011-08-20 Group-A 2011-08-21
8 Group-B USA VS Ireland 2011-08-21 Group-A 2011-08-22
9 Group-B USA VS Bangaladesh 2011-08-22 Group-A 2011-08-23
10 Group-B WestIndies VS Ireland 2011-08-23 Group-A 2011-08-24
11 Group-B WestIndies VS Bangaladesh2011-08-24 Group-A 2011-08-25
12 Group-B Ireland VS Bangaladesh 2011-08-25 NULL NULL
*/
Example 9:Using the Default Value of Lead function
This is also an optional argument.If not specified, then the implicit NULL value will be taken into account as the default which we have already seen in the previous examples. However, we can specify our own default value.
Select
MatchID
,MatchGroup
,MatchBetween
,ScheduleDate
,NextMatchBetween = Lead (MatchBetween,1,'No more Match') Over(Order by ScheduleDate)
,NextMatchDate = Lead (ScheduleDate,1) Over(Order by ScheduleDate)
From MatchTable
/*
MatchID MatchGroup MatchBetween ScheduleDate NextMatchBetween NextMatchDate
1 Group-A India VS Australia 2011-08-14 India VS Pakistan 2011-08-15
2 Group-A India VS Pakistan 2011-08-15 India VS Newzealand 2011-08-16
3 Group-A India VS Newzealand 2011-08-16 Australia VS Pakistan 2011-08-17
4 Group-A Australia VS Pakistan 2011-08-17 Australia VS Newzealand 2011-08-18
5 Group-A Australia VS Newzealand 2011-08-18 Newzealand VS Pakistan 2011-08-19
6 Group-A Newzealand VS Pakistan 2011-08-19 USA VS WestIndies 2011-08-20
7 Group-B USA VS WestIndies 2011-08-20 USA VS Ireland 2011-08-21
8 Group-B USA VS Ireland 2011-08-21 USA VS Bangaladesh 2011-08-22
9 Group-B USA VS Bangaladesh 2011-08-22 WestIndies VS Ireland 2011-08-23
10 Group-B WestIndies VS Ireland 2011-08-23 WestIndies VS Bangaladesh 2011-08-24
11 Group-B WestIndies VS Bangaladesh2011-08-24 Ireland VS Bangaladesh 2011-08-25
12 Group-B Ireland VS Bangaladesh 2011-08-25 No more Match<- Default value NULL
*/
The default date has been marked for easy understanding. If we read the graphical execution plan we can make out that,it is calling the Convert_Implicit method for the conversion of the default value specified
However, the default value’s datatype should be such that it can be converted by the engine as per the datatype of the scalar expression.Henceforth, if any value specified that the Convert_Implicit method fail to convert, then it will result into exception
Select
MatchID
,MatchGroup
,MatchBetween
,ScheduleDate
,NextMatchBetween = Lead (MatchBetween,1,'No more Match') Over(Order by ScheduleDate)
,NextMatchDate = Lead (ScheduleDate,1,'No more Match') Over(Order by ScheduleDate)
From MatchTable
/*
Msg 241, Level 16, State 1, Line 45
Conversion failed when converting date and/or time from character string.
*/
This statement failed because the default value should of date data type but we are passing varchar type.In order to avoid this,we can use try parse in conjunction with IIF as under
Declare @defaultValue VARCHAR(20) = 'No more Match'
Select
MatchID
,MatchGroup
,MatchBetween
,ScheduleDate
,NextMatchBetween = Lead (MatchBetween,1,@defaultValue) Over(Order by ScheduleDate)
,NextMatchDate = Lead ( ScheduleDate
,1
,IIF(
Try_Parse(@defaultValue as Date) Is Not Null
,@defaultValue
,Null
)
) Over(Order by ScheduleDate)
From MatchTable
Example 10 : Working with Partition by clause
It can work smoothly in conjunction with partition by clause as under
Select
MatchID
,MatchGroup
,MatchBetween
,ScheduleDate
,NextMatchBetween = Lead (MatchBetween,1,'No more Match in this group') Over(Partition By MatchGroup Order by ScheduleDate)
,NextMatchDate = Lead (ScheduleDate,1) Over(Partition By MatchGroup Order by ScheduleDate)
From MatchTable
Example 11 : Lead demonstration with partition by clause to restricted rows
We can even restrict rows as under
Select
MatchID
,MatchGroup
,MatchBetween
,ScheduleDate
,NextMatchBetween = Lead (MatchBetween,1,'No more Match in this group')
Over(Partition By MatchGroup Order by ScheduleDate)
,NextMatchDate = Lead (ScheduleDate,1)
Over(Partition By MatchGroup Order by ScheduleDate)
From MatchTable
Where MatchGroup = 'Group-A'
/* Result
MatchID MatchGroup MatchBetween ScheduleDate NextMatchBetween NextMatchDate
1 Group-A India VS Australia 2011-08-14 India VS Pakistan 2011-08-15
2 Group-A India VS Pakistan 2011-08-15 India VS Newzealand 2011-08-16
3 Group-A India VS Newzealand 2011-08-16 Australia VS Pakistan 2011-08-17
4 Group-A Australia VS Pakistan 2011-08-17 Australia VS Newzealand 2011-08-18
5 Group-A Australia VS Newzealand 2011-08-18 Newzealand VS Pakistan 2011-08-19
6 Group-A Newzealand VS Pakistan 2011-08-19 No more Match in this group NULL
*/
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 Lead function and it's various usage.Hope the article will be useful.
Thanks for reading