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.
 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() 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
|
