8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

NTH_VALUE Analytic Function

This article gives an overview of the NTH_VALUE analytic function, which is similar to the FIRST_VALUE and LAST_VALUE analytic functions. If you are new to analytic functions you should probably read this introduction to analytic functions first.

Related articles.

Setup

The examples in this article require the following table.

--DROP TABLE emp PURGE;

CREATE TABLE emp (
  empno    NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

NTH_VALUE Analytic Function

The basic description for the NTH_VALUE analytic function is shown below. The analytic clause is described in more detail here.

NTH_VALUE (measure_expr, n)
  [ FROM { FIRST | LAST } ][ { RESPECT | IGNORE } NULLS ] 
  OVER (analytic_clause)

The measure_expr is typically going to be a column, with "n" being the offset from the window boundary. Using the default FROM FIRST means we are counting to the Nth value from the top of the window. Using FROM LAST means we are counting back from the end of the window.

The lack of a partitioning clause in the OVER clause means the whole result set is treated as a single partition, so we get the Nth salary for all employees, as well as all the original data. The NTH_VALUE analytic function is order-sensitive, so it doesn't really make sense to use it without an ORDER BY in the analytic clause. Remember that as soon as you have an ORDER BY in the analytic clause you get the default window clause of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. You need to be sure this is what you need or you might get a unexpected result as seen below.

SELECT empno,
       ename,
       deptno,
       sal,
       NTH_VALUE(sal, 3) FROM FIRST OVER (ORDER BY sal) AS third_lowest_sal,
       NTH_VALUE(sal, 3) FROM LAST OVER (ORDER BY sal) AS third_highest_sal
FROM   emp;

     EMPNO ENAME          DEPTNO        SAL THIRD_LOWEST_SAL THIRD_HIGHEST_SAL
---------- ---------- ---------- ---------- ---------------- -----------------
      7369 SMITH              20        800
      7900 JAMES              30        950
      7876 ADAMS              20       1100             1100               800
      7521 WARD               30       1250             1100              1100
      7654 MARTIN             30       1250             1100              1100
      7934 MILLER             10       1300             1100              1250
      7844 TURNER             30       1500             1100              1250
      7499 ALLEN              30       1600             1100              1300
      7782 CLARK              10       2450             1100              1500
      7698 BLAKE              30       2850             1100              1600
      7566 JONES              20       2975             1100              2450
      7788 SCOTT              20       3000             1100              2975
      7902 FORD               20       3000             1100              2975
      7839 KING               10       5000             1100              3000

SQL>

The function using FROM FIRST gives us the result we expect, which is the third lowest salary if one exists. In contrast the function using FROM LAST call seems to be giving us a LAG of 2, rather than the third highest salary. This is because of the default windowing clause. What we wanted in this case was the following.

SELECT empno,
       ename,
       deptno,
       sal,
       NTH_VALUE(sal, 3) FROM FIRST OVER (ORDER BY sal) AS third_lowest_sal,
       NTH_VALUE(sal, 3) FROM LAST OVER (ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS third_highest_sal
FROM   emp;

     EMPNO ENAME          DEPTNO        SAL THIRD_LOWEST_SAL THIRD_HIGHEST_SAL
---------- ---------- ---------- ---------- ---------------- -----------------
      7369 SMITH              20        800                               3000
      7900 JAMES              30        950                               3000
      7876 ADAMS              20       1100             1100              3000
      7521 WARD               30       1250             1100              3000
      7654 MARTIN             30       1250             1100              3000
      7934 MILLER             10       1300             1100              3000
      7844 TURNER             30       1500             1100              3000
      7499 ALLEN              30       1600             1100              3000
      7782 CLARK              10       2450             1100              3000
      7698 BLAKE              30       2850             1100              3000
      7566 JONES              20       2975             1100              3000
      7788 SCOTT              20       3000             1100              3000
      7902 FORD               20       3000             1100              3000
      7839 KING               10       5000             1100              3000

SQL>

Adding the partitioning clause allows us to limit the search within a partition. In the following case we want to display the second smallest and second largest salary, in addition to the employee data.

SELECT empno,
       ename,
       deptno,
       sal,
       NTH_VALUE(sal, 2) FROM FIRST OVER (PARTITION BY deptno ORDER BY sal) AS third_lowest_sal,
       NTH_VALUE(sal, 2) FROM LAST OVER (PARTITION BY deptno ORDER BY sal 
                                         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS third_highest_sal
FROM   emp;

     EMPNO ENAME          DEPTNO        SAL THIRD_LOWEST_SAL THIRD_HIGHEST_SAL
---------- ---------- ---------- ---------- ---------------- -----------------
      7934 MILLER             10       1300                               2450
      7782 CLARK              10       2450             2450              2450
      7839 KING               10       5000             2450              2450
      7369 SMITH              20        800                               3000
      7876 ADAMS              20       1100             1100              3000
      7566 JONES              20       2975             1100              3000
      7788 SCOTT              20       3000             1100              3000
      7902 FORD               20       3000             1100              3000
      7900 JAMES              30        950                               1600
      7654 MARTIN             30       1250             1250              1600
      7521 WARD               30       1250             1250              1600
      7844 TURNER             30       1500             1250              1600
      7499 ALLEN              30       1600             1250              1600
      7698 BLAKE              30       2850             1250              1600

SQL>

Quick Links

The "*" indicates the function supports the full analytic syntax, including the windowing clause.

AVG * CLUSTER_DETAILS CLUSTER_DISTANCE CLUSTER_ID CLUSTER_PROBABILITY
CLUSTER_SET CORR * COUNT * COVAR_POP * COVAR_SAMP *
CUME_DIST DENSE_RANK FEATURE_DETAILS FEATURE_ID FEATURE_SET
FEATURE_VALUE FIRST FIRST_VALUE * LAG LAST
LAST_VALUE * LEAD LISTAGG MATCH_RECOGNIZE MAX *
MEDIAN MIN * NTH_VALUE * NTILE PERCENT_RANK
PERCENTILE_CONT PERCENTILE_DISC PREDICTION PREDICTION_COST PREDICTION_DETAILS
PREDICTION_PROBABILITY PREDICTION_SET RANK RATIO_TO_REPORT REGR_ (Linear Regression) Functions *
ROW_NUMBER STDDEV * STDDEV_POP * STDDEV_SAMP * SUM *
VAR_POP * VAR_SAMP * VARIANCE * String Aggregation Top-N Queries

For more information see:

Hope this helps. Regards Tim...

Back to the Top.