Oracle LAST_VALUE

Summary: in this tutorial, you will learn how to use the Oracle LAST_VALUE() function to return the last value in an ordered set of values.

Introduction to the Oracle LAST_VALUE() function

The LAST_VALUE() is an analytic function that allows you to obtain the last value in an ordered set of values.

The following shows the syntax of the Oracle LAST_VALUE() function:

LAST_VALUE (expression) [ {RESPECT | IGNORE} NULLS ])
OVER (
    [ query_partition_clause ] 
    order_by_clause
    [frame_clause]
)Code language: SQL (Structured Query Language) (sql)

In this syntax:

expression

is an expression evaluated against the value of the last row in the window frame specified by the frame_clause.

It is required that the expression to return a single value. In addition, the expression must not contain subqueries or analytic functions. The LAST_VALUE() function returns NULL if the value of the last row in the window frame evaluates to NULL unless you specify the IGNORE NULLS option. The function uses RESPECT NULLS by default even if you don’t explicitly specify it.

query_partition_clause

The query_partition_clause clause distributes rows into partitions by one or more criteria to which the LAST_VALUE() function is applied.

The query_partition_clause clause is optional. If you omit it, the function will treat the whole result set as a single partition.

The following shows the syntax of the query_partition_clause:

PARTITION BY expression1 [,expression2, ...]
Code language: SQL (Structured Query Language) (sql)

order_by_clause

The order_by_clause clause specifies the order of the rows in each partition to which the LAST_VALUE() function is applied. The order_by_clause is required and has the following form:

ORDER BY expression1 [,expression2,...] [ASC | DESC ] [NULLS FIRST | LAST]Code language: SQL (Structured Query Language) (sql)

frame_clause

The frame_clause defines the frame of the current partition.

Oracle LAST_VALUE() function examples

We’ll use the products table from the sample database to demonstrate the LAST_VALUE() function.

The following example returns the product id, product name, list price, and the name of the product that has the highest list price:

SELECT 
    product_id, 
    product_name, 
    list_price,
    LAST_VALUE(product_id) OVER (
        ORDER BY list_price 
        RANGE BETWEEN UNBOUNDED PRECEDING AND 
            UNBOUNDED FOLLOWING) highest_price_product_id
FROM 
    products;Code language: SQL (Structured Query Language) (sql)

Here is the partial output after scrolling down to the last page:

Note that the frame clause:

RANGE BETWEEN UNBOUNDED PRECEDING AND 
              UNBOUNDED FOLLOWINGCode language: SQL (Structured Query Language) (sql)

defines that the window frame starts at the first row and ends at the last row of the result set.

To show the highest price product in each category, you add the PARTITION BY clause as shown in the following query:

SELECT 
    product_id, 
    product_name, 
    category_id,
    list_price,
    LAST_VALUE(product_name) OVER (
        PARTITION BY category_id
        ORDER BY list_price 
        RANGE BETWEEN UNBOUNDED PRECEDING AND 
            UNBOUNDED FOLLOWING) highest_price_product_id
FROM 
    products;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to get the last value in a set of values by using the Oracle LAST_VALUE() function.