Oracle NTH_VALUE

Summary: in this tutorial, you will learn how to use the Oracle NTH_VALUE() function to get the N^th value in a set of values.

Introduction to Oracle NTH_VALUE() function

The Oracle NTH_VALUE() function is an analytic function that returns the N^th value in a set of values.

The following shows the syntax of the NTH_VALUE() function:

NTH_VALUE (expression, N)
[ FROM { FIRST | LAST } ]
[ { RESPECT | IGNORE } NULLS ] 
OVER (
    [ query_partition_clause ] 
    order_by_clause
    [frame_clause]
)Code language: SQL (Structured Query Language) (sql)

In this syntax:

expression

is any valid expression evaluated against the N^th row of the window frame.

N

Specifies the N^th row in the window frame defined by the frame_clause. N must be a positive integer such as 1, 2, and 3.

The NTH_VALUE() function will return NULL if the N^th row does not exist.

FROM { FIRST | LAST }

This determines whether the calculation starts at the first or last row of the window frame. The default is FROM FIRST.

 [ { RESPECT | IGNORE } NULLS ]

This determines whether NULL is included in or eliminated from the calculation. The default is RESPECT NULLS.

query_partition_clause

The query_partition_clause clause divides the rows into partitions to which the NTH_VALUE() function is applied. The query_partition_clause clause is not mandatory. If you skip it, the NTH_VALUE() function will treat the whole result set as a single partition.

order_by_clause

The order_by_clause clause specifies the order of rows in each partition to which the NTH_VALUE() function is applied.

frame_clause

The frame_clause defines the frame of the current partition.

Oracle NTH_VALUE() function examples

We will use the products table from the sample database for the demonstration:

Using Oracle NTH_VALUE() function to find the nth value

The following example uses the NTH_VALUE() function to return all the products and also the second most expensive one:

SELECT
    product_id,
    product_name,
    list_price,
    NTH_VALUE(product_name,2) OVER (
        ORDER BY list_price DESC
        RANGE BETWEEN 
            UNBOUNDED PRECEDING AND 
            UNBOUNDED FOLLOWING
    ) AS second_most_expensive_product
FROM
    products;
Code language: SQL (Structured Query Language) (sql)

The following picture shows the partial output:

Using Oracle NTH_VALUE() function to find the nth value in each partition

The following query uses the NTH_VALUE() function to get all the products as well as the second most expensive product by category:

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

In this tutorial, you have learned how to use the Oracle NTH_VALUE() function to get the N^th value in a set of values.