Oracle 11gR2 NTH_VALUE Function Usage Syntax

Oracle 11gR2 has a new analytical function to find out the nth value in a window. Using this function you can find out the nth largest value from a set of values or by grouping some set of values.

{RESPECT | IGNORE} NULLS determine whether null values of measure_expr are included in or eliminated from the calculation. The default is RESPECT NULLS.

FROM {FIRST | LAST} determines whether the calculation begins at the first or last row of the window. The default is FROM FIRST.

See some examples below.

Here is my sample EMP table

SQL> select * from emp order by dept, sal;

EMP_NAME                             DEPT        SAL
------------------------------ ---------- ----------
Jain                                   10       4000
Rupal                                  10       5000
Hero                                   10       5500
Roshan                                 10       5500
David                                  10
John                                   20       6000
Sam                                    20       6500
Riju                                   20       7000
Roshan                                 30       5500
Kiran                                  30       5500
Peter                                  30       6800
King                                   30       7600

12 rows selected.

  1. Query to find out 3rd or Nth value from the EMP table
SQL> select emp_name, dept, sal , nth_value(sal,3) over (partition by dept order by dept ) second from emp a
where sal=(select distinct nth_value(sal,3) over (partition by dept order by dept) from emp where dept=a.dept); 

EMP_NAME                             DEPT        SAL     SECOND
------------------------------ ---------- ---------- ----------
Hero                                   10       5500
Roshan                                 10       5500
Riju                                   20       7000
Peter                                  30       6800

  1. Query to find out 4th Largest from the Last with Ignore nulls. By default nth_value function considers the respect nulls

SQL> select emp_name, dept, sal , nth_value(sal,4) over (partition by dept order by dept ) second from emp a
 where sal=(select distinct nth_value(sal,4)
 from last ignore nulls over (partition by dept order by dept) from emp where dept=a.dept);

EMP_NAME                             DEPT        SAL     SECOND
------------------------------ ---------- ---------- ----------
Jain                                   10       4000
Roshan                                 30       5500
Kiran                                  30       5500

  1. Query to find out 3rd Largest from the Last with Respect nulls.

SQL> select emp_name, dept, sal , nth_value(sal,3) over (partition by dept order by dept ) second from emp a
 where sal=(select distinct nth_value(sal,3) from last over (partition by dept order by dept) from emp where dept=a.dept);

EMP_NAME                             DEPT        SAL     SECOND
------------------------------ ---------- ---------- ----------
Hero                                   10       5500
Roshan                                 10       5500
John                                   20       6000
Roshan                                 30       5500
Kiran                                  30       5500

  1. Query to find out 2nd Largest with all other columns of the table.

SQL> select emp_name, dept, sal , nth_value(sal,2) over (partition by dept order by dept ) second from emp;

EMP_NAME                             DEPT        SAL     SECOND
------------------------------ ---------- ---------- ----------
Jain                                   10       4000       5000
Rupal                                  10       5000       5000
Hero                                   10       5500       5000
Roshan                                 10       5500       5000
John                                   20       6000       6500
Sam                                    20       6500       6500
Riju                                   20       7000       6500
Kiran                                  30       5500       5500
Roshan                                 30       5500       5500
Peter                                  30       6800       5500
King                                   30       7600       5500

  1. Query to find out 2nd Largest from First (Default) with all other columns of the table.

SQL> select emp_name, dept, sal , nth_value(sal,2) from first over (partition by dept order by dept ) second_large from emp;

EMP_NAME                             DEPT        SAL SECOND_LARGE
------------------------------ ---------- ---------- ------------
Jain                                   10       4000         5000
Rupal                                  10       5000         5000
Hero                                   10       5500         5000
Roshan                                 10       5500         5000
David                                  10                    5000
John                                   20       6000         6500
Sam                                    20       6500         6500
Riju                                   20       7000         6500
Roshan                                 30       5500         5500
Kiran                                  30       5500         5500
Peter                                  30       6800         5500
King                                   30       7600         5500

12 rows selected.

  1. Query to find out 2nd Largest from Last with all other columns of the table.

SQL> select emp_name, dept, sal , nth_value(sal,2) from last over (partition by dept order by dept ) second_large from emp;

EMP_NAME                             DEPT        SAL SECOND_LARGE
------------------------------ ---------- ---------- ------------
Jain                                   10       4000         5500
Rupal                                  10       5000         5500
Hero                                   10       5500         5500
Roshan                                 10       5500         5500
David                                  10                    5500
John                                   20       6000         6500
Sam                                    20       6500         6500
Riju                                   20       7000         6500
Roshan                                 30       5500         6800
Kiran                                  30       5500         6800
Peter                                  30       6800         6800
King                                   30       7600         6800

12 rows selected.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google