The SQL LAST_VALUE is an Analytic function that returns the Last Value in an ordered set of values. The basic syntax of the LAST_VALUE in SQL Server is as shown below:
SELECT LAST VALUE([Scalar Expression]) OVER ( PARTITION_BY_Clause ORDER_BY_Clause ) FROM [Source]
For this LAST_VALUE demonstration, we use the below-shown data
SQL LAST_VALUE without Partition By Clause
What if we miss the Partition By Clause in the LAST_VALUE Function.
SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,[HireDate] ,LAST_VALUE([FirstName]) OVER (ORDER BY ([Sales])) AS Customer FROM [Employee]
OUTPUT
ANALYSIS
Above SQL LAST_VALUE code returns the last value of the First Name based on the Sales column.
The below line of SQL Server code sort table data based on the Sales data in an Ascending order
ORDER BY [Sales]
Let me show you the same
USE [SQL Tutorial] GO SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,[HireDate] FROM [Employee] ORDER BY [Sales]
OUTPUT
The LAST_VALUE function in the below code returns the independent Last Value based on the Sales. In the above example, you can see 24.99 for Christy, and Rob. From those two names, LastName will select the Rob and return the same
LAST_VALUE([FirstName]) OVER (ORDER BY ([Sales])) AS Customer
All the remaining values are independent, so LAST_VALUE function is returning the same.
LAST_VALUE with Partition By Example
This sql last_value example shows how to select the Last value from the partitioned records. First, it will partition the data by Occupation based on the Sales, and then select the Last [First Name] value in each partition.
SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,[HireDate] ,LAST_VALUE([FirstName]) OVER ( PARTITION BY [Occupation] ORDER BY [Sales] ) AS BestCustomer FROM [Employee]
OUTPUT