[Oracle] 11g R2 : NTH_VALUE Function

 

NTH_VALUE Function

The NTH_VALUE function enables you to find column values from an arbitrary row in the window. This could be used when, for example, you want to retrieve the 5th highest closing price for a company's shares during a year.

The LAG and LEAD functions can be thought of as being related to, and a simplification of, the NTH_VALUE function. With LAG and LEAD, you can only retrieve values from a row at the specified physical offset. If this is insufficient, you can use NTH_VALUE, which enables you to retrieve values from a row based on what is called a logical offset or relative position. You can use the IGNORE NULLS option with the NTH_VALUE, FIRST_VALUE, and LAST_VALUE functions to make it more powerful, in the sense that you can specify conditions and filter out rows based on certain conditions. See Example 22-17, "NTH_VALUE", where rows with quantities less than eight are filtered out. This cannot be done with LAG or LEAD, as you would not know the offset to the row.

See Oracle Database SQL Language Reference for more information.

This function has syntax as follows:

NTH_VALUE (<expr>, <n expr>) [FROM FIRST | FROM LAST]
[RESPECT NULLS | IGNORE NULLS] OVER (<window specification>)

• expr can be a column, constant, bind variable, or an expression involving them.

• n can be a column, constant, bind variable, or an expression involving them.

• RESPECT NULLS is the default NULL handling mechanism. It determines whether null values of expr are included in or eliminated from the calculation. The default is RESPECT NULLS.

• The FROM FIRST and FROM LAST options determine whether the offset n is from the first or last row. The default is FROM FIRST.

• IGNORE NULLS enables you to skip NULLs in measure values.

Example 22-17 NTH_VALUE

The following example returns the amount_sold value of the second channel_id in ascending order for each prod_id in the range between 10 and 20:

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 10 AND 20 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
...