PLSQL: Working with NULLs and Function NHT_VALUE

Did you know this function?

NTH_VALUE returns the result of expression on value of the nth row in the window defined by the analytic clause. The returned value has the data type of the expression. RESPECT NULLS or IGNORE clauses can be used to determine whether null values of expression are included in or eliminated from the calculation. The default is RESPECT NULLS. Also clauses FROM FIRST or FROM LAST determines whether the calculation begins at the first or last row of the window. The default is FROM FIRST.

The function returns NULL if the data source window has fewer than n rows. If n is null, then the function returns an error.

Example:

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

For more information and examples, check: https://docs.oracle.com/cloud/latest/db112/SQLRF/functions114.htm#SQLRF30031

Cheers!