SQL LAST_VALUE

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