SQL Reference Guide

NTH_VALUE windowing function

Declaration

NTH_VALUE ( { expression, offset } ) OVER ( [ partition_by_clause ] order_by_clause )

Description

The window function NTH_VALUE returns the value of the expression at the row specified by the offset within the window frame.

• The argument expression can be of any type.
• •The expression must return a single value.
• The argument offset is a positive integer that specifies the row number relative to the first row in the window.

The result type of NTH_VALUE matches the type of the argument expression. If the offset specifies a row outside the range of the window, NTH_VALUE returns NULL.

Example

select c1, c2, c3, nth_value(c2, 2) over (partition by c3 order by c2) from nth;

C1 C2 C3 NTH_VALUE(C2

-- -- -- ------------

1 1 1

2 2 1 2

3 2 2

4 3 2 3

5 3 3

5 records selected