LAST VALUE Function in SQL Server

LAST VALUE Function in SQL Server

The LAST_VALUE() SQL Server analytical function is used to return last value in an ordered set of list. The syntax for LAST_VALUE() function is as follows,

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

The scalar_expression is an expression that returns single value. It does not permit other analytic functions. The partition_by_clause splits the returned list into partitions, Otherwise the function treats all the result list as a single group. The order_by_clause specifies which order the result set should be displayed?. The rows_range_clause specifies the range of rows to be returned in a result set.
Lets see this in detail using an example,

We are going to use "tbl_BankDetails" table for our demo.

CREATE TABLE [dbo].[tbl_BankDetails](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[AccNo] [varchar](450) NULL,
[Month] [bigint] NULL,
[Balance] [decimal](18, 0) NULL,
 CONSTRAINT [PK_tbl_BankDetails] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-- insert some demo data
insert into tbl_BankDetails(AccNo, Month,Balance) values(100,2,1000);
insert into tbl_BankDetails(AccNo, Month,Balance) values(100,3,1050);
insert into tbl_BankDetails(AccNo, Month,Balance) values(100,4,1200);
insert into tbl_BankDetails(AccNo, Month,Balance) values(100,6,1300);
insert into tbl_BankDetails(AccNo, Month,Balance) values(100,8,1100);
insert into tbl_BankDetails(AccNo, Month,Balance) values(100,9,1400);
insert into tbl_BankDetails(AccNo, Month,Balance) values(101,1,1900);
insert into tbl_BankDetails(AccNo, Month,Balance) values(101,2,1100);
insert into tbl_BankDetails(AccNo, Month,Balance) values(101,4,1750);
insert into tbl_BankDetails(AccNo, Month,Balance) values(101,5,1400);
insert into tbl_BankDetails(AccNo, Month,Balance) values(101,7,1100);
insert into tbl_BankDetails(AccNo, Month,Balance) values(102,2,1350);
insert into tbl_BankDetails(AccNo, Month,Balance) values(102,4,1100);
insert into tbl_BankDetails(AccNo, Month,Balance) values(102,6,1040);
insert into tbl_BankDetails(AccNo, Month,Balance) values(102,8,1025);

insert into tbl_BankDetails(AccNo, Month,Balance) values(102,9,1104);

So our table look like this,

Now, Lets see how to use LAST_VALUE() function in a query:

select *,
LAST_VALUE(Balance) OVER(Order by Month) as LastBalance
 from tbl_BankDetails

The returned result is,

In the above example the LAST_VALUE function produces the last balance of each month that you can see in the LastBalance column.

In order to get the last value to remain the same for all rows in the result set, we have to use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING like in the below query:

select *,
LAST_VALUE(Balance) OVER(Order by AccNo ROWS
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as LastBalance
from tbl_BankDetails

The returned result is,

The above example returns the last value of the column (Balance) in a LastBalance result set.
Now lets see how to apply partition by in our query?

select *,
LAST_VALUE(Balance) OVER(partition by AccNo Order by Id
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as LastBalance
from tbl_BankDetails

The above query splits the result set by account number and returns the last balance based on the last row of each account number.
The returned result is,