advertisements
_____________________________________________________________________________________________________________________
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.
- 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
- 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
- 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
- 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
- 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.
- 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.
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment