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
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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

SQL Server

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

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.