Last_Value
Purpose: Returns the last value from the order set of values.
Syntax: Last_Value(expression) over( [ Partition_By_clause] order by clause [rows_range_clause])
Where,
Expression => A table column or built-in function but not analytical functions
Rows_range_clause => It helps to further limit the effect of analytical function.
As said, the Last _Value function returns the last value from the order set of values. If the last value is null, then the function returns null.
Before going to look into some examples of First_Value we will first create the test environment
Run the below script for doing so
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'MatchTable' AND type = 'U')
DROP TABLE MatchTable
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE [dbo].[MatchTable](
[MatchID] [int] IDENTITY(1,1) NOT NULL,
[MatchGroup] [varchar](8) NULL,
[MatchBetween] [varchar](50) NULL,
[ScheduleDate] [date] NULL
) ON [PRIMARY]
GO
--Insert records
Insert Into MatchTable Values
('Group-A','India VS Australia','08/14/2011')
,('Group-A','India VS Pakistan','08/15/2011')
,('Group-A','India VS Newzealand','08/16/2011')
,('Group-A','Australia VS Pakistan','08/17/2011')
,('Group-A','Australia VS Newzealand','08/18/2011')
,('Group-A','Newzealand VS Pakistan','08/19/2011')
,('Group-B','USA VS WestIndies','08/20/2011')
,('Group-B','USA VS Ireland','08/21/2011')
,('Group-B','USA VS Bangaladesh','08/22/2011')
,('Group-B','WestIndies VS Ireland','08/23/2011')
,('Group-B','WestIndies VS Bangaladesh','08/24/2011')
,('Group-B','Ireland VS Bangaladesh','08/25/2011')
-- Project the records
Select * From MatchTable
/* Result
MatchID MatchGroup MatchBetween ScheduleDate
1 Group-A India VS Australia 2011-08-14
2 Group-A India VS Pakistan 2011-08-15
3 Group-A India VS Newzealand 2011-08-16
4 Group-A Australia VS Pakistan 2011-08-17
5 Group-A Australia VS Newzealand 2011-08-18
6 Group-A Newzealand VS Pakistan 2011-08-19
7 Group-B USA VS WestIndies 2011-08-20
8 Group-B USA VS Ireland 2011-08-21
9 Group-B USA VS Bangaladesh 2011-08-22
10 Group-B WestIndies VS Ireland 2011-08-23
11 Group-B WestIndies VS Bangaladesh2011-08-24
12 Group-B Ireland VS Bangaladesh 2011-08-25
*/
Let us see this function into action.
Example 1: Simple Last_Value demo
Suppose we need to find out the last match played in every group. We can achieve it as under
Select
MatchBetween
,MatchGroup
,Last_Value(MatchBetween) Over(Partition By MatchGroup Order By MatchGroup) As [Last Match In Group]
From MatchTable
/* Result
MatchBetween MatchGroup Last Match In Group
------------ --------- ---------------------
India VS Australia Group-A Newzealand VS Pakistan
India VS Pakistan Group-A Newzealand VS Pakistan
India VS Newzealand Group-A Newzealand VS Pakistan
Australia VS Pakistan Group-A Newzealand VS Pakistan
Australia VS Newzealand Group-A Newzealand VS Pakistan
Newzealand VS Pakistan Group-A Newzealand VS Pakistan
USA VS WestIndies Group-B Ireland VS Bangaladesh
USA VS Ireland Group-B Ireland VS Bangaladesh
USA VS Bangaladesh Group-B Ireland VS Bangaladesh
WestIndies VS Ireland Group-B Ireland VS Bangaladesh
WestIndies VS BangaladeshGroup-B Ireland VS Bangaladesh
Ireland VS Bangaladesh Group-B Ireland VS Bangaladesh
*/
Example 2: Last_Value with enhanced over clause
We have already covered Over clause enhancement .It's time to now see it's effect with the Last_Value
Select
MatchBetween
,ScheduleDate
,Last_Value(MatchBetween) Over(Order By ScheduleDate Range Unbounded Preceding) As Rng_UPrec
,Last_Value(MatchBetween) Over(Order By ScheduleDate Rows Unbounded Preceding) As Row_UPrec
,Last_Value(MatchBetween) Over(Order By ScheduleDate Range Current Row) As Rng_Curr
,Last_Value(MatchBetween) Over(Order By ScheduleDate Rows Current Row) As Row_Curr
,Last_Value(MatchBetween) Over(Order By ScheduleDate Range Between Unbounded Preceding And Current Row) As Rng_UPrec_Curr
,Last_Value(MatchBetween) Over(Order By ScheduleDate Rows Between Unbounded Preceding And Current Row) As Row_UPrec_Curr
,Last_Value(MatchBetween) Over(Order By ScheduleDate Range Between Unbounded Preceding And Unbounded Following) As Rng_UPrec_UFoll
,Last_Value(MatchBetween) Over(Order By ScheduleDate Rows Between Unbounded Preceding And Unbounded Following) As Row_UPrec_UFoll
,Last_Value(MatchBetween) Over(Order By ScheduleDate Range Between Current Row And Unbounded Following) As Rng_Curr_UFoll
,Last_Value(MatchBetween) Over(Order By ScheduleDate Rows Between Current Row And Unbounded Following) As Row_Curr_UFoll
From MatchTable
/* Result
MatchBetween ScheduleDate Rng_UPrec Row_UPrec Rng_Curr Row_Curr Rng_UPrec_Curr Row_UPrec_Curr Rng_UPrec_UFoll Row_UPrec_UFoll Rng_Curr_UFoll Row_Curr_UFoll
Ireland VS Bangaladesh 2011-08-25 Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh
WestIndies VS Bangaladesh 2011-08-24 WestIndies VS Bangaladesh WestIndies VS Bangaladesh WestIndies VS Bangaladesh WestIndies VS Bangaladesh WestIndies VS Bangaladesh WestIndies VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh
WestIndies VS Ireland 2011-08-23 WestIndies VS Ireland WestIndies VS Ireland WestIndies VS Ireland WestIndies VS Ireland WestIndies VS Ireland WestIndies VS Ireland Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh
USA VS Bangaladesh 2011-08-22 USA VS Bangaladesh USA VS Bangaladesh USA VS Bangaladesh USA VS Bangaladesh USA VS Bangaladesh USA VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh
USA VS Ireland 2011-08-21 USA VS Ireland USA VS Ireland USA VS Ireland USA VS Ireland USA VS Ireland USA VS Ireland Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh
USA VS WestIndies 2011-08-20 USA VS WestIndies USA VS WestIndies USA VS WestIndies USA VS WestIndies USA VS WestIndies USA VS WestIndies Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh
Newzealand VS Pakistan 2011-08-19 Newzealand VS Pakistan Newzealand VS Pakistan Newzealand VS Pakistan Newzealand VS Pakistan Newzealand VS Pakistan Newzealand VS Pakistan Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh
Australia VS Newzealand 2011-08-18 Australia VS Newzealand Australia VS Newzealand Australia VS Newzealand Australia VS Newzealand Australia VS Newzealand Australia VS Newzealand Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh
Australia VS Pakistan 2011-08-17 Australia VS Pakistan Australia VS Pakistan Australia VS Pakistan Australia VS Pakistan Australia VS Pakistan Australia VS Pakistan Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh
India VS Newzealand 2011-08-16 India VS Newzealand India VS Newzealand India VS Newzealand India VS Newzealand India VS Newzealand India VS Newzealand Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh
India VS Pakistan 2011-08-15 India VS Pakistan India VS Pakistan India VS Pakistan India VS Pakistan India VS Pakistan India VS Pakistan Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh
India VS Australia 2011-08-14 India VS Australia India VS Australia India VS Australia India VS Australia India VS Australia India VS Australia Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh Ireland VS Bangaladesh
*/
Hope this helps