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.

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 #