Last_value in SQL server

 The Last_value function returns value from the last row of the window. This allow a query to access value from multiple rows at once without doing self-join. In this function over clause with order by is mandatory to have an ordered result set.

Syntax

LAST_VALUE (Column Name) OVER 

([partition_by_clause col1, col2,.] ORDER BY Col1, 

Col2, .. FRAME_CLAUSE)

partition_by_clause divides the rows of the result sets into partitions to which the function applies. It is an optional.

order_by_clause specify the order in which the operation is performed. it is required.

frame_clause defines the subset (or frame) of the partition being evaluated. Will see its in the example.

Let’s see the example

We have a table.

create table employee( id int identity(1,1) primary key, empid int, emp_name varchar(50), salary_withdraw decimal(18,10), withdraw_dt    date )

Inserting some records in this table.

insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(1,'Bagesh',1002,'01-31-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(1,'Bagesh',1000,'02-29-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(1,'Bagesh',1020,'03-31-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(1,'Bagesh',1050,'04-30-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(1,'Bagesh',1030,'05-31-2024');   insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(2,'Rajesh',2002,'02-29-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(2,'Rajesh',2009,'03-31-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(2,'Rajesh',2000,'04-30-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(2,'Rajesh',1999,'05-31-2024');   insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(3,'Mohan',1012,'02-29-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(3,'Mohan',1052,'03-31-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(3,'Mohan',1082,'04-30-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(3,'Mohan',1002,'05-31-2024');   insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(4,'Sohan',87,'02-29-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(4,'Sohan',77,'03-31-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(4,'Sohan',97,'04-30-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(4,'Sohan',67,'05-31-2024');

See the records in the table.

Basic use of LAST_VALUE Function

In the following example, LAST_VALUE function returns the highest paid salary from the entire table, and if more than one employee having same salary then sort the record based on empid in ascending order.

select empid,emp_name,salary_withdraw,withdraw_dt, LAST_VALUE(salary_withdraw) over(order by salary_withdraw rows between unbounded preceding and unbounded following) as highest_salary, LAST_VALUE(salary_withdraw) over(order by salary_withdraw desc rows between unbounded preceding and unbounded following) as lowest_salary  from employee

   

If we will not use frame_clause we will not get the correct value. See below

select empid,emp_name,salary_withdraw,withdraw_dt, LAST_VALUE(salary_withdraw) over(order by salary_withdraw ) as highest_salary, LAST_VALUE(salary_withdraw) over(order by salary_withdraw desc ) as lowest_salary  from employee

So if we are using Last_value function we need to use frame_clause.

Above statement using a FRAME_CLAUSE,ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING Which tells the LAST_VALUE function that it’s frame starts at the first row (UNBOUNDED PRECEDING ) and ends at the last row (UNBOUNDED FOLLOWING) in the result set.

LAST_VALUE function with PARTITION CLAUSE

In the following example, LAST_VALUE function returns the highest salary employee in withdraw month.  

select empid,emp_name,salary_withdraw,withdraw_dt, LAST_VALUE(salary_withdraw) over(partition by empid order by salary_withdraw rows between unbounded preceding and unbounded following) as highest_salary, LAST_VALUE(salary_withdraw) over(partition by empid order by salary_withdraw desc rows between unbounded preceding and unbounded following) as lowest_salary  from employee