SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server 2012 Download and T-SQL Tutorials
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012

SQL LAG() Function in SQL Server 2012 for Calculating Previous Value

SQL LAG() function is one of the recent enhancements among SQL Analytic Functions introduced with Denali, CTP3 for SQL Server 2012 developers. What SQL Server Lag() function does is returning simply the previous-next Nth row value in an order. The order of the rows can be grouped by using the Partition By clause for powering the SQL Lag() function.

Here is the SQL Analytic LAG() function syntax for T-SQL developers

LAG (scalar_expression [, offset], [default]) OVER ([partition_by_clause] order_by_clause)

The Lag() function input offset argument determines the number of previous rows in order that the SQL Engine will read from. Offset input parameter is optional. If nothing is provided, then the default value 1 will be used in LAG() function

The default argument sets the value which will be returned if SQL Lag() function returns nothing.

Here is our first SQL Server Lag() function example. If you look at the returned result set of the below SQL query, you will realize that all data from AnalyticFunctions table is listed. Besides the value of the previous row when ordered by Id column with the same Category value is displayed in PreviousValue column.

You see, the Lag() function offset argument is 1 as default value. This returns the previous row value. And the Lag() function default argument is NULL. Since the first row for each category does not have a previous row, the SQL LAG() function cannot calculate the PreviousValue column, and returns the default NULL value.

 LAG(Value, 1, NULL) OVER (Partition By Category ORDER BY Id) AS PreviousValue
FROM AnalyticFunctions

TSQL LAG() function in SQL Server 2012 for calculation previous value

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums

Copyright © 2004 - 2020 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems