Tutorial Gateway

SQL LAST_VALUE

by

The SQL LAST_VALUE is one of the Analytic Function in SQL Server. This function will return the Last Value in an ordered set of values. In this article we will show you, How to use the analytic function called LAST_VALUE in SQL Server 2014 with example. Before we get into the example, let us see the syntax behind this:

SQL LAST_VALUE Function Syntax

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]
  • Scalar Expression: It can be a Column, Subquery, or any expression that returns single value.
  • Order_By_Clause: It is used to sort the Partitioned data in a specific order. I suggest you to refer SQL Order By Clause for better understanding.
  • Partition_By_Clause: This will divide the records selected by the SELECT Statement into partitions.

For this demonstration, We are going to use the below shown data

SQL LAST_VALUE 1

SQL LAST_VALUE without Partition By Clause

The SQL LAST_VALUE function will return the Last Value in an ordered set of values. In this example we will show you, What will happen if we miss the Partition By Clause in the LAST_VALUE Function.

T-SQL CODE

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

OUTPUT

SQL LAST_VALUE 2

ANALYSIS

Above code is a general select statement, and we are writing the last value of the First Name based on the Sales column. Below statement will sort the Employee table based on the Sales data in an Ascending order

ORDER BY [Sales]

Let me show you the same

T-SQL CODE

USE [SQL Tutorial]
GO
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
FROM [Employee]
ORDER BY [Sales]

OUTPUT

SQL LAST_VALUE 3

Now, LAST_VALUE function in the below code snippet will return 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, Last-Name will select the Rob and return the same

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

All the remaining values are independent so, function is returning the same.

SQL LAST_VALUE with Partition By Example

In this example we will show you, How to select the Last value from the partitioned records present in a table. The following SQL Query will partition the data by Occupation based on the Sales, and then select the Last [First Name] value in an ordered list within each partition.

T-SQL CODE

USE [SQL Tutorial]
GO
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
      ,LAST_VALUE([FirstName]) OVER (
                                      PARTITION BY [Occupation] 
				      ORDER BY [Sales]
				     ) AS BestCustomer   
FROM [Employee]

OUTPUT

SQL LAST_VALUE 4

ANALYSIS

Below statement will divide the selected data into partition using their Occupation. From the above you can observe, We got four partitions

PARTITION BY [Occupation]

Below statement will sort the partitioned data in the descending order using their [Sales]

ORDER BY [Sales]

In the below statement we used LAST_VALUE Function with Partition by clause. It will independently select the last records from each partition.

LAST_VALUE([FirstName]) OVER (
                               PARTITION BY [Occupation] 
	   	               ORDER BY [Sales]
			     ) AS BestCustomer

Thank You for Visiting Our Blog

Placed Under: SQL