SQL Server LAST_VALUE() Function

The LAST_VALUE() function is a window function in SQL Server used to retrieve the last value in a group. This function can be used in a SELECT statement to return the last value for each row in a group. The LAST_VALUE() function must be used with an OVER clause.

Syntax

The syntax for the LAST_VALUE() function is as follows:

LAST_VALUE(expression) OVER (
  [PARTITION BY partition_expression, ... ]
  ORDER BY order_expression [ASC | DESC]
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

where:

• expression: specifies the column or expression to return the last value of.
• PARTITION BY: optional, specifies the column or expression to group by.
• ORDER BY: specifies the column or expression to sort rows within each partition.
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: specifies the window size to compute the last value for each row.

Usage

The LAST_VALUE() function is typically used to retrieve the last value in a group. For example, if there is a table containing order history records, we can use the LAST_VALUE() function to retrieve the final status of each order. Additionally, the LAST_VALUE() function can also be used to handle time-series data to retrieve the last value for each time period.

Examples

Example 1

Suppose there is a table named sales with the following data:

We can use the LAST_VALUE() function to retrieve the last sales for each product. The following query and result:

SELECT DISTINCT
  product_id,
  LAST_VALUE(sales) OVER (PARTITION BY product_id ORDER BY order_id) AS last_sales
FROM sales;

Results:

Example 2

Suppose there is a table named stocks with the following data:

We can use the LAST_VALUE() function to retrieve the last price for each stock code. The following query and result:

SELECT DISTINCT
  stock_code,
  LAST_VALUE(price) OVER (PARTITION BY stock_code ORDER BY date) AS last_price
FROM stocks;

Results:

Assuming we want to obtain the last price and the previous day’s price for each stock code. To do this, we can add the ROWS BETWEEN clause to the LAST_VALUE() function and set it to UNBOUNDED PRECEDING and 1 PRECEDING. This way, we can get the last and previous values for each row. The following is the query statement and result:

SELECT DISTINCT
  stock_code,
  LAST_VALUE(price) OVER (PARTITION BY stock_code ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_price,
  LAST_VALUE(price) OVER (PARTITION BY stock_code ORDER BY date) AS last_price
FROM stocks;

Query Result:

In the query result above, we can see the last price and the previous day’s price for each stock code. Since there is no previous day’s price on the first day, a NULL value is displayed in the prev_price column.

Conclusion

The LAST_VALUE() function is a useful tool in SQL Server for obtaining the last value in a grouping. It can help us process tables containing time-series data and find the last value for each time period. When using the LAST_VALUE() function, it is important to specify parameters such as grouping, sorting, and window size to ensure that the correct results are obtained.