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 |
