Oracle NTH_VALUE Function

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.

Here’s the basic syntax of the NTH_VALUE() function:

NTH_VALUE (expression, N) [ FROM { FIRST | LAST } ] [ { RESPECT | IGNORE } NULLS ] OVER ( [ 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.

partition_clause #

The partition_clause clause divides the rows into partitions to which the NTH_VALUE() function is applied. The 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’ll 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)

Try it

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)

Try it

Summary #

Use the Oracle NTH_VALUE() function to get the N^th value in a set of values.

Quiz #

Was this tutorial helpful?