Oracle NTH_VALUE Function

FIRST_VALUE and LAST_VALUE cover the ends of a window. NTH_VALUE returns the value from any position: the second hire, the third-highest fare, the second most recent order. It can count from the first row or from the last, and skip NULLs.

Code for This Guide

The main examples are in the examples/analytic-functions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

nth_value(expr, n) [from {first | last}] [{respect | ignore} nulls]
  over ([partition by ...] order by ... [frame])

n is the position, from 1. FROM FIRST (the default) counts from the start of the window, FROM LAST from its end. If the window has fewer than n rows, the result is NULL.

The Second Hire

Example:

select department_id, last_name, hire_date,
       first_value(last_name) over w as first_hired,
       last_value(last_name)  over w as last_hired,
       nth_value(last_name, 2) over w as second_hired
from   employees
where  department_id in (50, 60)
window w as (partition by department_id order by hire_date
             rows between unbounded preceding and unbounded following)
order  by department_id, hire_date;

Output:

   DEPARTMENT_ID LAST_NAME    HIRE_DATE      FIRST_HIRED    LAST_HIRED    SECOND_HIRED
________________ ____________ ______________ ______________ _____________ _______________
              50 Mehta        01-SEP-2015    Mehta          Schmidt       Reddy
              50 Reddy        06-JUN-2016    Mehta          Schmidt       Reddy
              50 Williams     18-FEB-2019    Mehta          Schmidt       Reddy
              50 Schmidt      04-SEP-2023    Mehta          Schmidt       Reddy
              60 Okafor       01-FEB-2016    Okafor         Mensah        Costa
              60 Costa        29-JAN-2018    Okafor         Mensah        Costa
              60 Mensah       09-MAY-2022    Okafor         Mensah        Costa

7 rows selected.

NTH_VALUE(last_name, 2) returns the second hire of each department on every row: Reddy and Costa. The frame covers the whole partition; with the default frame, the first row of each department would get NULL, since its window has only one row.

From the Last Row, Ignoring NULLs

Example:

-- the second most recent hire, counting from the last, and with IGNORE NULLS
select distinct department_id,
       nth_value(last_name, 2) from last over w as second_latest,
       nth_value(commission_pct, 1) ignore nulls over w as first_commission
from   employees
where  department_id in (40, 50)
window w as (partition by department_id order by hire_date
             rows between unbounded preceding and unbounded following)
order  by department_id;

Output:

   DEPARTMENT_ID SECOND_LATEST       FIRST_COMMISSION
________________ ________________ ___________________
              40 Gupta                           0.05
              50 Williams

FROM LAST with n = 2 gives the second most recent hire. With IGNORE NULLS, NTH_VALUE(commission_pct, 1) skips the employees without a commission and returns the first commission in hiring order; department 50 has none, so the result is NULL.

Things to Know

• NTH_VALUE(x, 1) equals FIRST_VALUE(x) for the same window.
• n can be an expression, but must be a positive integer.
• The DISTINCT in the example removes the repetition, since analytic functions return a value on every row.

Related Guides

• Oracle FIRST_VALUE Function
• Oracle LAST_VALUE Function

Conclusion

NTH_VALUE returns the value from the nth row of the window, counted from the first or the last row, optionally ignoring NULLs. Give it a frame that covers the rows you mean, usually the whole partition.