Nth_Value - Analytics 2.0

The new Analytic window function Nth_Value gives users the functionality of retrieving an arbitrary Nth record in a window.

Here is the common routine to pick up the second maximum salary for each departments.

rajesh@10GR2> SELECT empno,
  2  deptno     ,
  3  sal        ,
  4  MAX(temp) over(partition BY deptno) AS second_sal
  5  FROM
  6    (SELECT empno,
  7      deptno     ,
  8      sal        ,
  9      CASE
 10        WHEN row_number() over(partition BY deptno order by sal) = 2
 11        THEN sal
 12        ELSE NULL
 13      END AS temp
 14       FROM emp
 15    )
 16  ORDER BY deptno,sal
 17  /

     EMPNO     DEPTNO        SAL SECOND_SAL
---------- ---------- ---------- ----------
      7934         10       1300       2450
      7782         10       2450       2450
      7839         10       5000       2450
      7369         20        800       1100
      7876         20       1100       1100
      7566         20       2975       1100
      7788         20       3000       1100
      7902         20       3000       1100
      7900         30        950       1250
      7521         30       1250       1250
      7654         30       1250       1250
      7844         30       1500       1250
      7499         30       1600       1250
      7698         30       2850       1250

14 rows selected.

Elapsed: 00:00:00.01

Now, the same using Nth_Value window function in 11gR2.

rajesh@11GR2> SELECT empno,
  2  deptno     ,
  3  sal        ,
  4  nth_value(sal,2) over(partition BY deptno
  5          order by sal
  6          range BETWEEN unbounded preceding AND unbounded following) AS second_sal
  7   FROM emp
  8  ORDER BY deptno, sal
  9  /

     EMPNO     DEPTNO        SAL SECOND_SAL
---------- ---------- ---------- ----------
      7934         10       1300       2450
      7782         10       2450       2450
      7839         10       5000       2450
      7369         20        800       1100
      7876         20       1100       1100
      7566         20       2975       1100
      7788         20       3000       1100
      7902         20       3000       1100
      7900         30        950       1250
      7654         30       1250       1250
      7521         30       1250       1250
      7844         30       1500       1250
      7499         30       1600       1250
      7698         30       2850       1250

14 rows selected.

Elapsed: 00:00:00.04
rajesh@11GR2>
rajesh@11GR2>