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, samples, codes and tools for .Net, SQL Server, Vista, etc.




download SQL Server 2014



SQL LAG() Function in SQL Server 2012 for Calculating Preivous 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 previousnext 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.

SELECT
 *,
 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





Follow Kodyaz on Twitter

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers










Copyright © 2004 - 2014 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems