SQL Server Portal

In my previous Post, I discussed about FIRST_VALUE.Today, we will discuss another important analytical function namely “LAST_VALUE”. We can also say this is the reverse of FIRST_VALUE analytical function.

Lets discuss this function syntax, purpose and examples in detail.

Syntax :

LAST_VALUE ( [scalar_expression ) OVER ( [ partition_by_clause ] order_by_clause rows_range_clause )

Purpose :
It returns the Last value from a list in a specified order. Also we can define partition & rows range parameter in this function, but these are optional parameters. By default Row range is between unbounded preceding and current row or range unbounded preceding.
It is better to define “row range” parameter in the LAST_Value function. Later in this article, we will also discuss the importance of “row range” parameter in LAST_VALUE.

Lets create a student table and insert few records in it to demonstrate LAST_VALUE.

Create Table [Student]
(
[Student ID] int,
[Student Name] varchar(50),
[Subject] varchar(50),
[Marks] int
)
GO

Insert into [Student] values (3,'Derek','English',100)
Insert into [Student] values (3,'Derek','Math',60)
Insert into [Student] values (3,'Derek','Science',90)

Insert into [Student] values (1,'Bob','English',80)
Insert into [Student] values (1,'Bob','Math',75)
Insert into [Student] values (1,'Bob','Science',60)

Insert into [Student] values (2,'Sandra','English',70)
Insert into [Student] values (2,'Sandra','Math',80)
Insert into [Student] values (2,'Sandra','Science',40)

Select * from [Student] Order By [Student ID]

Example-1 :
In this example, LAST_VALUE returns the Last Name of the student in the class (In alphabetical order) . And the expected result is “Sandra”.

Select
[Student ID]
,[Student Name]
,[Subject]
,[Marks]
,LAST_Value([Student Name]) Over (Order By [Student Name])
as [Last Value]
from dbo.[Student]
--Result

But Unfortunately, this is not our expected result, It is the same row value in Last Value &  student name column.

Here “row range ” optional parameters of LAST_VALUE comes to solve this problem. Now, we need to change the “row range” value from default to CURRENT ROW AND UNBOUNDED FOLLOWING.

Lets make another example with row range CURRENT ROW AND UNBOUNDED FOLLOWING.

Select
[Student ID]
,[Student Name]
,[Subject]
,[Marks]
,LAST_Value([Student Name]) Over (Order By [Student Name]
ROWS BETWEEN  CURRENT ROW AND UNBOUNDED FOLLOWING) as [Last Value]
from dbo.[Student]
--Result

Now, we got our expected result by defining “row range”.

Example-2 :
In this example, LAST_VALUE returns the student name having least score in the class. Expected result is : “Sandra”. Because she is having least score (40) in science in the entire class.

Select
[Student ID]
,[Student Name]
,[Subject]
,[Marks]
,LAST_Value([Student Name]) Over (Order By [Marks] Desc
ROWS BETWEEN  CURRENT ROW AND UNBOUNDED FOLLOWING) AS [LAST Value]
from dbo.[Student]
--Result

Example-3 :
In this example, LAST_VALUE returns the student name having least score in each subject in the class. Expected result is : Sandra in “English & Science” & Derek in “Maths”.

Select
[Student ID]
,[Student Name]
,[Subject]
,[Marks]
,LAST_Value([Student Name]) Over (Partition By [Subject]
Order By [Marks] Desc
ROWS BETWEEN  CURRENT ROW AND UNBOUNDED FOLLOWING) AS [LAST Value]
from dbo.[Student]
--Result

Reference : MSDN