With this blog, I am trying to explain the default behavior of LAST_VALUE analytic function. By default LAST_VALUE fails to give us LAST VALUE from the partitioned data set (window) and gives unexpected results, which creates confusion in minds of many developers.
To explain the issue, let me first write a simple SQL with FIRST_VALUE function.
As we can see in above snippet, FIRST_VALUE worked fine, and we easily got the first value from every window set i.e. first salary (lowest) of each department with salary in ascending order.
Now lets rewrite the same SQL using LAST_VALUE analytic function.
We were expecting the LAST_VALUE function to return the last value from every window set i.e. last salary (highest) of each department with salary in ascending order, but LAST_VALUE failed to give us the desired results. This is a very common issue with LAST_VALUE, which many developers face.
This happened because default behavior of an ORDER BY clause in a partition window is "BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW". Which means LAST_VALUE can only get values from the current rows or the rows before it in the current window set. To resolve the issue we need to expend the window size to complete data set of each partition using windowing clause "ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING".
As you can see here LAST_VALUE worked as a charm with this little tweak. I hope this post was able to make you learn something new or was at-least a good revision for you.
Please do post your feedback in comment box. Enjoy SQL :)
Related Posts:
- Oracle: Rank, Dense_Rank, Row_Number Analytic Functions
- Oracle: Getting Cumulative Sum (Running Total) Using Analytical Functions
- LAG and LEAD - Analytic Function in Oracle Database
- FIRST_VALUE and LAST_VALUE with Windowing Clause
- Top N query - 3rd highest salary in Oracle Database
- Partitioned Outer Join - Fill Gaps in Sparse Data
To explain the issue, let me first write a simple SQL with FIRST_VALUE function.
nimish@garg> select EMPNO, ENAME, JOB, MGR, HIREDATE, COMM, DEPTNO, SAL,
2 FIRST_VALUE(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL) FIRST_SAL
3 FROM EMP;
EMPNO ENAME JOB MGR HIREDATE COMM DEPTNO SAL FIRST_SAL
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 10 1300 1300
7782 CLARK MANAGER 7839 09-JUN-81 10 2450.45 1300
7839 KING PRESIDENT 17-NOV-81 10 5000.15 1300
7369 SMITH CLERK 7902 17-DEC-80 20 800.75 800.75
7876 ADAMS CLERK 7788 23-MAY-87 20 1100.01 800.75
7566 JONES MANAGER 7839 02-APR-81 20 2975.5 800.75
7788 SCOTT ANALYST 7566 19-APR-87 20 3000.55 800.75
7902 FORD ANALYST 7566 03-DEC-81 20 3000.6 800.75
7900 JAMES CLERK 7698 03-DEC-81 30 950.99 950.99
7521 WARD SALESMAN 7698 22-FEB-81 500 30 1250 950.99
7654 MARTIN SALESMAN 7698 28-SEP-81 1400 30 1250.05 950.99
7844 TURNER SALESMAN 7698 08-SEP-81 0 30 1500.95 950.99
7499 ALLEN SALESMAN 7698 20-FEB-81 300 30 1600.9 950.99
7698 BLAKE MANAGER 7839 01-MAY-81 30 2850.3 950.99
As we can see in above snippet, FIRST_VALUE worked fine, and we easily got the first value from every window set i.e. first salary (lowest) of each department with salary in ascending order.
Now lets rewrite the same SQL using LAST_VALUE analytic function.
nimish@garg> select EMPNO, ENAME, JOB, MGR, HIREDATE, COMM, DEPTNO, SAL,
LAST_VALUE(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL) LAST_SAL
FROM EMP;
EMPNO ENAME JOB MGR HIREDATE COMM DEPTNO SAL LAST_SAL
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 10 1300 1300
7782 CLARK MANAGER 7839 09-JUN-81 10 2450.45 2450.45
7839 KING PRESIDENT 17-NOV-81 10 5000.15 5000.15
7369 SMITH CLERK 7902 17-DEC-80 20 800.75 800.75
7876 ADAMS CLERK 7788 23-MAY-87 20 1100.01 1100.01
7566 JONES MANAGER 7839 02-APR-81 20 2975.5 2975.5
7788 SCOTT ANALYST 7566 19-APR-87 20 3000.55 3000.55
7902 FORD ANALYST 7566 03-DEC-81 20 3000.6 3000.6
7900 JAMES CLERK 7698 03-DEC-81 30 950.99 950.99
7521 WARD SALESMAN 7698 22-FEB-81 500 30 1250 1250
7654 MARTIN SALESMAN 7698 28-SEP-81 1400 30 1250.05 1250.05
7844 TURNER SALESMAN 7698 08-SEP-81 0 30 1500.95 1500.95
7499 ALLEN SALESMAN 7698 20-FEB-81 300 30 1600.9 1600.9
7698 BLAKE MANAGER 7839 01-MAY-81 30 2850.3 2850.3
We were expecting the LAST_VALUE function to return the last value from every window set i.e. last salary (highest) of each department with salary in ascending order, but LAST_VALUE failed to give us the desired results. This is a very common issue with LAST_VALUE, which many developers face.
This happened because default behavior of an ORDER BY clause in a partition window is "BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW". Which means LAST_VALUE can only get values from the current rows or the rows before it in the current window set. To resolve the issue we need to expend the window size to complete data set of each partition using windowing clause "ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING".
nimish@garg> select EMPNO, ENAME, JOB, MGR, HIREDATE, COMM, DEPTNO, SAL,
2 LAST_VALUE(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LAST_SAL
3 FROM EMP;
EMPNO ENAME JOB MGR HIREDATE COMM DEPTNO SAL LAST_SAL
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 10 1300 5000.15
7782 CLARK MANAGER 7839 09-JUN-81 10 2450.45 5000.15
7839 KING PRESIDENT 17-NOV-81 10 5000.15 5000.15
7369 SMITH CLERK 7902 17-DEC-80 20 800.75 3000.6
7876 ADAMS CLERK 7788 23-MAY-87 20 1100.01 3000.6
7566 JONES MANAGER 7839 02-APR-81 20 2975.5 3000.6
7788 SCOTT ANALYST 7566 19-APR-87 20 3000.55 3000.6
7902 FORD ANALYST 7566 03-DEC-81 20 3000.6 3000.6
7900 JAMES CLERK 7698 03-DEC-81 30 950.99 2850.3
7521 WARD SALESMAN 7698 22-FEB-81 500 30 1250 2850.3
7654 MARTIN SALESMAN 7698 28-SEP-81 1400 30 1250.05 2850.3
7844 TURNER SALESMAN 7698 08-SEP-81 0 30 1500.95 2850.3
7499 ALLEN SALESMAN 7698 20-FEB-81 300 30 1600.9 2850.3
7698 BLAKE MANAGER 7839 01-MAY-81 30 2850.3 2850.3
As you can see here LAST_VALUE worked as a charm with this little tweak. I hope this post was able to make you learn something new or was at-least a good revision for you.
Please do post your feedback in comment box. Enjoy SQL :)
Related Posts:
- Oracle: Rank, Dense_Rank, Row_Number Analytic Functions
- Oracle: Getting Cumulative Sum (Running Total) Using Analytical Functions
- LAG and LEAD - Analytic Function in Oracle Database
- FIRST_VALUE and LAST_VALUE with Windowing Clause
- Top N query - 3rd highest salary in Oracle Database
- Partitioned Outer Join - Fill Gaps in Sparse Data
--Display Dept wise max Sal
ReplyDelete--First_value with order deptno,sal desc(display dept wise max sal)
select e.* , first_value(sal ignore nulls) over(partition by e.deptno order by deptno,sal desc) H_sal
from emp e ;
--last_value with out order by in window fun, with regular order by sal asc (display dept wise max sal )
select e.* , last_value(sal) over(partition by e.deptno) H_sal
from emp e
order by deptno,sal asc;
--Display Dept wise min Sal
--First_value with order deptno,sal asc(display dept wise min sal)
select e.* , first_value(sal ignore nulls) over(partition by e.deptno order by deptno,sal) H_sal
from emp e ;
--last_value with out order by in window fun, with out regular order by (display dept wise min sal)
select e.* , last_value(sal) over(partition by e.deptno) H_sal
from emp e;
--last_value with out order by in window fun, with regular order by sal desc(display dept wise min sal)
select e.* , last_value(sal) over(partition by e.deptno) H_sal
from emp e
order by deptno,sal desc;
Nice explanation
ReplyDeleteGreat explanation - thanks!
ReplyDeletethank you somuch... it helped me
ReplyDelete