Skip Headers
Oracle® Database SQL Language Reference
11g Release 2 (11.2)

Part Number E17118-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

NTH_VALUE

Syntax

Description of nth_value.gif follows
Description of the illustration nth_value.gif

See Also:

"Analytic Functions" for information on syntax, semantics, and restrictions of the analytic_clause

Purpose

NTH_VALUE returns the measure_expr value of the nth row in the window defined by the analytic_clause. The returned value has the data type of the measure_expr.

See Also:

Oracle Database Data Warehousing Guide for more information on the use of this function

Examples

The following example shows the minimum amount_sold value for the second channel_id in ascending order for each prod_id between 13 and 16:

SELECT prod_id, channel_id, MIN(amount_sold),
    NTH_VALUE(MIN(amount_sold), 2) OVER (PARTITION BY prod_id ORDER BY channel_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) nv
  FROM sales
  WHERE prod_id BETWEEN 13 and 16
  GROUP BY prod_id, channel_id;

   PROD_ID CHANNEL_ID MIN(AMOUNT_SOLD)         NV
---------- ---------- ---------------- ----------
        13          2           907.34      906.2
        13          3            906.2      906.2
        13          4           842.21      906.2
        14          2          1015.94    1036.72
        14          3          1036.72    1036.72
        14          4           935.79    1036.72
        15          2           871.19     871.19
        15          3           871.19     871.19
        15          4           871.19     871.19
        16          2           266.84     266.84
        16          3           266.84     266.84
        16          4           266.84     266.84
        16          9            11.99     266.84

13 rows selected.