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 paid employee in each Department, and if more than one employee for any department 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 (PARTITION BY EMP_DEPT ORDER BY EMP_SALARY ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS LASTVALUE FROM EMP
As you can see, It returns Highest paid employee name in each department against each rows.
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 ‘LAKSHYA RAWAT’ is comes in LAST ROW.
Also Read..
1,567 total views, 2 views today