LEAD and LAG functions were highly awaited and demanded function in SQL Server and finally it is now introduced in SQL Server 2012. I really like some of new functions introduced in SQL Server 2012 which I have listed in my earlier blog post. If you wish, you can access those articles from the links given below:
New Datetime Functions in SQL Server 2012 (Click Here)
New PARSE, TRY_PARSE & TRY_CONVERT conversion functions in SQL Server 2012 (Click Here)
New LAST_VALUE and FIRST_VALUE Analytic function in SQL Server 2012 (Click Here)
CUME_DIST and PERCENT_RANK new analytic functions in SQL Server 2012 (Click Here)
Well, now moving on!!!
Do you remember when you wanted to get previous and next value of particular column for particular row? I do remember it and I know I have used self-join, CTE, cursor , loop and many different type of logical solution but now our life become little easier with two new analytic functions in SQL Server which are known as LEAD & LAG.
LEAD: This function accesses the data from next row without any help of self-join or CTE.
LAG: This function accesses the data from previous row without any help of self-join or CTE.
Though, definition of these functions is short but use of these functions is really big and save you from developing customized logic many time. Let us understand this by small example here.
IF OBJECT_ID('tempdb..#AnalyticFunctionCheck') IS NOT NULL DROP TABLE #AnalyticFunctionCheck CREATE TABLE #AnalyticFunctionCheck ( ID INT IDENTITY(1,1) ,ClientID VARCHAR(10) ,TestName VARCHAR(20) ,TotalSample INT ) INSERT INTO #AnalyticFunctionCheck SELECT 'CHEM02','VOCMS GROUP 1',4 UNION ALL SELECT 'CHEM02','SVOC GROUP 1',6 UNION ALL SELECT 'CHEM02','SVOC STARS',12 UNION ALL SELECT 'CHEM02','PESTICIDE',4 UNION ALL SELECT 'SHAW01','PESTICIDE',10 UNION ALL SELECT 'SHAW01','SVOC STARS',3 UNION ALL SELECT 'SHAW01','SVOC GROUP 1',9 UNION ALL SELECT 'EPAW01','SVOC GROUP 1',14 SELECT * FROM #AnalyticFunctionCheck SELECT * ,LAG(ID, 1) OVER (ORDER BY ID) AS PreviousQuota ,LEAD(ID, 1) OVER (ORDER BY ID) AS PreviousQuota FROM #AnalyticFunctionCheck SELECT * ,LAG(ID, 2) OVER (ORDER BY ID) AS PreviousQuota ,LEAD(ID, 2) OVER (ORDER BY ID) AS PreviousQuota FROM #AnalyticFunctionCheck
After executing above given 3 queries, you will find result set like this:
First result set is simple “SELECT *” query on the temp table we created.
Second result set had offset “1” (refer LAG(ID, 1)). 1 is a default offset and it provides previous row if it is LAG function and next row if it is LEAD function.
Third result set returns ID of 2nd previous/Next row as we have provided offset “2” (refer LAG(ID, 2)) based on the function LEAD/LAG provided.
If function will not find previous/next value, it will return NULL by default.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.