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

Last_Value SQL Analytic Function in SQL Server 2012 T-SQL

Last_Value() SQL analytic function returns the last value of an ordered set of values.
SQL Last_Value() function provides unique solutions to TSQL problems with Partition By and Rows Range specifications.

Last_Value() function is one of the newest enhancements in T-SQL just like other new SQL Server analytic functions introduced with SQL Server 2012

Here is the SQL syntax for Last_Value() function

LAST_VALUE(scalar_expression) OVER ([Partition_By_clause] Order_By_clause Rows_Range_clause)

Scalar expression in Last_Value() can be an expression or a subquery returning a single value as well as a column value. Scalar expression in First_Value() is obligatory

Using Partition By clause is optional and enables developers to group result set into subsets by partitioning. So that the Last_Value() analytic function can be applied to each partitioned subset seperately.

Order By clause is obligatory and sorts the result set

Rows Range clause is obligatory in Last_Value() function opposite to its optional use in SQL First_Value() function.
rows_range clause further limits the return set. For example, tsql programmers can limit Last_Value() function to apply to rows up to current row in sorted result set. Or developers can limit the set that Last_Value() function will work over with previous 11 rows and with current row providing a yearly plan for instance.

rows range clause default behavior Row_Range enables t-sql programmers provide advanced solutions with new SQL Server Analytic Functions. But if you will not use it in Last_Value() function or in other functions, the default behaviour can fail you and your T-SQL query. Since the default window frame value used is RANGE UNBOUNDED PRECEDING AND CURRENT ROW. In order to apply analytic function to overall result set range between unbounded preceding and unbounded following or rows between unbounded preceding and unbounded following rows_range specifications can be selected.

SQL Server Last_Value() Function Samples

In this T-SQL tutorial, I'ld like to demonstrate SQL Last_Value() function examples over SQL Server 2012 sample database AdventureWorks

SQL Last_Value() Function with Range Rows Clause

By mistake, actually since I missed the importance of Rows_Range_clause I thought that I experienced unexpected results from SQL Server Last_Value() function execution. I've also submitted a bug form on Microsoft Connect web site bug form. But Umachandar from SQL Programmability Team has explained me my mistake with a good example. I've adapted his example here in order to emphasize the importance of the Rows Range Clause in SQL Last_Value() function.

Here is a basic t-sql sample code utilizing Last_Value() analytic function in SQL Server 2012

Create Table LastValueTable (Id int)
insert into LastValueTable Values (1),(2),(3),(4)

 Last_Value(Id) OVER (Order By Id) as LV1_CURRENT_ROW,
 Last_Value(Id) OVER (Order By Id range unbounded preceding) as LV2_CURRENT_ROW,
 Last_Value(Id) OVER (Order By Id range between unbounded preceding and current row) as LV3_CURRENT_ROW,
 Last_Value(Id) OVER (Order By Id rows between unbounded preceding and unbounded following) as LV4_LAST_ROW_BY_ROWS,
 Last_Value(Id) OVER (Order By Id range between unbounded preceding and unbounded following) as LV5_LAST_ROW_BY_RANGE
from LastValueTable

SQL Last_Value() analytic function sample

SQL Last_Value() Function with Partition By Clause

Here is an other SQL Last_Value function example from AdventureWorks2008R2 SQL Server sample database. This time Last_Value() function is used with Partition By clause is used

The following T-SQL Select statement will return all sales order details, with two additional column. One column is for the last date when the same product is ordered. And the second column is the order number in which the same product is ordered recently.

 Last_Value(ModifiedDate) OVER (Partition By ProductID Order By ModifiedDate rows between unbounded preceding and unbounded following),
 Last_Value(SalesOrderID) OVER (Partition By ProductID Order By ModifiedDate rows between unbounded preceding and unbounded following)
from Sales.SalesOrderDetail

T-SQL programmers and SQL Server data professionals can have a look at other new SQL Analytic Functions introduced with SQL Server 2012

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.