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 and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



Last_Value SQL Analytic Function in SQL Server 2011 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 2011, Denali CTP3 release.

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 2011 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 2011, Denali CTP3

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

select
 Id,
 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.

select
 ProductID,
 ModifiedDate,
 SalesOrderID,
 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 201, Denali CTP3







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







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