Search
Winners

Win Prizes

Social Presence
Twitter Twitter LinkedIn YouTube Google

Like us on Facebook
Advertisements
Top Articles Author
Wed, 30-Jul-2014 Authors
All Time Authors
Sourav.Kayal
39750
Sheonarayan
37300
Niladri.Biswas
33350

Latest members | More ...


(Statistics delayed by 5 minutes)
Ads

Let's learn Lead Function in Sql Server 2012

Niladri.Biswas
Posted by under Sql Server category on for Beginner level | Points: 250 | Views : 1360 red flag
If you found plagiarised (copied) or inappropriate content,
please let us know the original source along with your correct email id (to communicate) for further action.
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

Page copy protected against web site content infringement by Copyscape
About the Author

Niladri.Biswas

Full Name: Niladri Biswas
Member Level: Platinum
Member Status: Member
Member Since: 10/25/2010 11:04:24 AM
Country: India
Best Regards, Niladri Biswas
http://www.dotnetfunda.com

Technical Lead at HCL Technologies
Login to vote for this post.
Found interesting? Add this to:


Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)