SQL Server LAST_VALUE function

SQL Server LAST_VALUE function is a analytic function which returns the Last value in an ordered set of values. 

Syntax 

 LAST_VALUE(Column_Name) OVER ( [partition_by_clause col1, col2,.] 
 ORDER BY Col1, Col2, ... FRAME_CLAUSE)

Column_Name
Is the value to be returned.  

OVER ( [partition_by_clause col1, col2,.] ORDER BY Col1, Col2, … )

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 use in query statement.

Lets look at an example of LAST_VALUE in SQL Server.

First, we create a sample table named as EMP, and insert some records in the table.

CREATE TABLE dbo.EMP
(EMPID INT PRIMARY KEY, 
EMP_DEPT VARCHAR(50), 
EMP_NAME VARCHAR(30), 
EMP_SALARY NUMERIC(9,2))
INSERT INTO dbo.EMP (
EMPID, EMP_DEPT, EMP_NAME, EMP_SALARY)
VALUES(101, 'PRODUCTION', 'RAJAT M', 75000.00),
(102, 'PRODUCTION', 'MUKESH BHATIA', 70000.00),
(103, 'PRODUCTION', 'MUSKAN MEHTA', 75000.00),
(104, 'SALES', 'ROHAN B NARAYA', 45000.00),
(105, 'SALES', 'SUSHIL DAS', 40000.00),(106, 'SALES', 'MANISH', 45000.00),
(107, 'PRODUCTION', 'RAJESH SINGH', 78000.00),
(108, 'HR', 'MOHIN KHAN',50000.00),
(109, 'HR', 'SUSHANT K SINGH', 55000.00),
(110, 'HR', 'LAKSHYA RAWAT', 55000.00),
(111, 'PRODUCTION', 'MANOJ KUMAR', 75000.00),
(112, 'SALES', 'SUJOY M', 40000.00),
(113, 'LOGISTIC', 'VINAY AGARWAL', 35000.00),
(114, 'LOGISTIC', 'MUSTAKIM M', 35000.00),
(115, 'LOGISTIC', 'VIJAY KUMAWAT', 45000.00)

Basic use of LAST_VALUE Function

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

SELECT EMPID, EMP_DEPT, EMP_NAME, EMP_SALARY,
LAST_VALUE(Emp_Name) OVER ( ORDER BY EMP_SALARY, 
EMP_NAME ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS LASTTVALUE
FROM EMP

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.

As you can see, the output of above statement, it returns the name of highest paid employee in column LASTVALUE against each rows.

LAST_VALUE function with PARTITION cLAUSE

In the following example,  LAST_VALUE function returns the name of the highest salary paid employee in each department, and if more than one employee getting same salary then sort the records based on employee’s name in ascending order.

SELECT EMPID, EMP_DEPT, EMP_NAME, EMP_SALARY,
LAST_VALUE(Emp_Name) OVER (PARTITION BY EMP_DEPT ORDER BY EMP_SALARY, EMP_Name ROWS 
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS LASTVALUE
FROM EMP

As you can see, it returns the name of highest salary paid employee for each department.

Also in HR department there are two employee who is getting same salary that is 55000, so sorting is done based on the their name in ascending order that’s why ‘SUSHANT K SINGH’ is comes in LAST ROW.

Also Read..

FIRST_VALUE()

LEAD()

LAG()