Oracle Database 11gR2 – New analytical function NTH_VALUE

You are probably familiar with the FIRST_VALUE and LAST_VALUE analytical functions that were introduced some time ago into the Oracle RDBMS, in the 9iR2 release I believe (or at least that is when they made their way into the Standard Edition). These values are used to find the first respectively last value in a window in a partition that has been ordered in a certain way.

Oracle introduces a new, related function in 11gR2, called NTH_VALUE. Instead of simply the first or last value in an analytical window, we can ask for a specific row number, such as 2nd row (ND_VALUE?), the 7th or the 223th. This article shows the syntax for this new function.

For example a query to return the colleague (same department) who has the highest salary:

select deptno
,      ename
,      first_value(ename)
       over (partition by deptno
             order by sal desc)
       most_earning_colleague
from   emp


    DEPTNO ENAME      MOST_EARNI
---------- ---------- ----------
        10 KING       KING
        10 CLARK      KING
        10 MILLER     KING
        20 FORD       FORD
        20 SCOTT      FORD
        20 JONES      FORD
        20 ADAMS      FORD
        20 SMITH      FORD
        30 BLAKE      BLAKE
        30 ALLEN      BLAKE
        30 TURNER     BLAKE
        30 MARTIN     BLAKE
        30 WARD       BLAKE
        30 JAMES      BLAKE

a similar query with LAST_VALUE (or FIRST_VALUE in combination with order by sal asc) would return the colleague with the lowest salary.

With NTH_VALUE we can target a specific row number. Using NTH_VALUE with target 1 is equivalent to using the FIRST_VALUE function (or LAST_VALUE with a reversed order by clause).

NTH_VALUE takes an expression – a column name or combination of columns and functions – and the index number of the requested row (the N that this function takes its name from). Then we can specify whether we want to start the count from the first or the last row using either from first or from last – note that this is optional and when omitted the default from first will be used. Next comes the normal analytical partition and window clause, the over() construct in which we indicate in what partition we want the window to be constructed and how the rows are ordered inside the window. This part of an NTH_VALUE expression is the same as for other analytical functions. Note that NTH_VALUE requires an order by expression to be used – after all how could nth_value be meaningful if the rows are not ordered in some way.

Let’s find out about everyone’s second highest earning colleague. We query the ename for the 2nd row (n=2) starting from the top (from first) in the partition defined by the department (the same department as the current employee) and with the records in the department partition ordered by salary in descending order:

select deptno
,      ename
,      sal
,      nth_value(ename,2) from first
       over (partition by deptno
             order by sal desc)
       almost_highest_earn_coll
from   emp
order
by     deptno
,      sal desc
/
    DEPTNO ENAME             SAL ALMOST_HIG
---------- ---------- ---------- ----------
        10 KING             5000
        10 CLARK            2450 CLARK
        10 MILLER           1300 CLARK
        20 FORD             3000 SCOTT
        20 SCOTT            3000 SCOTT
        20 JONES            2975 SCOTT
        20 ADAMS            1100 SCOTT
        20 SMITH             800 SCOTT
        30 BLAKE            2850
        30 ALLEN            1600 ALLEN
        30 TURNER           1500 ALLEN
        30 MARTIN           1250 ALLEN
        30 WARD             1250 ALLEN
        30 JAMES             950 ALLEN

Here we learn the useful information that CLARK is the second-highest earner in Department 10 while ALLEN has that position in Department 20. Riveting stuff.

To once more compare with FIRST_VALUE and LAST_VALUE:

Analytical Function NTH_VALUE returns the NTH_VALUE in a WINDOW

FIRST_VALUE(column) == NTH_VALUE(column,1)

LAST_VALUE(column) == NTH_VALUE(column,1) FROM LAST
 

FROM FIRST (default) and FROM LAST can be used to start counting at either end of the window

NTH_VALUE( sal ,2) over (order by sal desc)

==

NTH_VALUE( sal ,2) FROM FIRST over (order by sal desc)

==

NTH_VALUE( sal ,2) FROM LAST over (order by sal asc) ROWS UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING

Another example: a query to return all employees and per employee the difference between the highest earning colleague and the second highest earning colleague:

select deptno
,      ename
,      first_value(ename)
       over (partition by deptno
       order by sal desc)
       -
       nth_value(ename,2) from first
       over (partition by deptno
       order by sal desc)
       top2_difference
from   emp
/
  DEPTNO ENAME      TOP2_DIFFERENCE
-------- ---------- ---------------
      10 KING
      10 CLARK                 2550
      10 MILLER                2550
      20 SCOTT                    0
      20 FORD                     0
      20 JONES                    0
      20 ADAMS                    0
      20 SMITH                    0
      30 BLAKE
      30 ALLEN                 1250
      30 TURNER                1250
      30 MARTIN                1250
      30 WARD                  1250
      30 JAMES                 1250

Ignore nulls

IGNORE NULLS can be added to not count rows for which the expression evaluates to NULL. The default is RESPECT NULLS – which means that rows with null values for their expression are counted

NTH_VALUE(COMM, 2) IGNORE NULLS
OVER (ORDER BY SAL DESC)

means:

1. Order the employees by salary, the top earner first
2. Find the second row where COMM is not null
3. Return the value of COMM in that row

Resources

Oracle Database 11gR2 – SQL Language Reference – NTH_VALUE