Oracle NTH_VALUE

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

Introduction to Oracle NTH_VALUE() function

The Oracle NTH_VALUE() function is an analytic function that returns the Nth 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 Nth row of the window frame.

N

Specifies the Nth 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 Nth 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:

products table

Using Oracle NTH_VALUE() function to find 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:

Oracle NTH_VALUE Function Example

Using Oracle NTH_VALUE() function to find 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 Nth value in a set of values.

Was this tutorial helpful?