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 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)
- Order the employees by salary, the top earner first
- Find the second row where COMM is not null
- Return the value of COMM in that row
- The Next Value – How much higher is my salary than the next in (Dense) Rank – Oracle Analytical Functions
- Oracle RDBMS 11gR2 – LISTAGG – New aggregation operator for creating (comma) delimited strings
- Is this a bug in Analytical Functions? NULL is supposed to be UNEQUAL to NULL?
- SQL*Plus or Report style Break Groups in SQL Query
- Introduction to Analytical Functions – Use of Analytics as alternative for Simple Aggregations