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 is as shown below:

SELECT LAST VALUE([Scalar Expression]) 
                  OVER (
                         PARTITION_BY_Clause 
                         ORDER_BY_Clause
                        )
FROM [Source]

For this demonstration, we use the below-shown data

SQL LAST_VALUE without Partition By Clause

What if we miss the Partition By Clause in the Function?

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
      ,LAST_VALUE([FirstName]) OVER (ORDER BY ([Sales])) AS Customer 
  FROM [Employee]

The above SQL Server code returns the last value of the First Name based on the Sales column.

The below line of SQL LAST_VALUE code sort table data is based on the Sales data in Ascending order. Let me show you the same.

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
FROM [Employee]
ORDER BY [Sales]

The analytic 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. LastName will select the Rob from those two names and return the same.

LAST_VALUE([FirstName]) OVER (ORDER BY ([Sales])) AS Customer

All the remaining values are independent, so this function returns the same.

LAST_VALUE with Partition By Example

This SQL LAST_VALUE function example shows how to select the Last value from the partitioned records. First, the last_value query 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]