FIRST_VALUE and LAST_VALUE with Windowing Clause

Analytic functions compute an aggregate value based on a group of rows called window which determines the range of rows used to perform the calculations for the current row. Following are most used Analytic functions.
- RANK, DENSE_RANK and ROW_NUMBER
- LAG and LEAD
- FIRST_VALUE and LAST_VALUE

The FIRST_VALUE returns the first result of an ordered set from a window. The LAST_VALUE returns the last result of an ordered set from a window

Now the main thing here to understand is when we use order by clause in window clause the default windowing clause is set to "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" and makes LAST_VALUE to result unexpected. We need to alter the windowing clause to "RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" get the result which is expected from LAST_VALUE.

A windowing clause defines the group (or window) of rows within a particular partition that will be evaluated for analytic function computation.
- UNBOUNDED PRECEDING indicate that the window starts at the first row of the partition.
- UNBOUNDED FOLLOWING indicate that the window ends at the last row of the partition.
- CURRENT ROW specifies that the window begins or end at the current row or value depending on where you have used CURRENT ROW. like

range between CURRENT ROW and unbounded following
range between unbounded preceding and CURRENT ROW

Lets have simple examples of FIRST_VALUE and LAST_VALUE using EMP table of SCOTT schema.

FIRST_VALUE:

SQL> select
  2    empno, ename, deptno, sal,
  3    first_value(sal) over (partition by deptno order by sal) first_sal
  4  from scott.emp;

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

14 rows selected.

LAST_VALUE:

SQL> select
  2    empno, ename, deptno, sal,
  3    last_value(sal) over (partition by deptno order by sal
  4      range between unbounded preceding and unbounded following) last_sal
  5  from scott.emp;

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

14 rows selected.

If we execute QUERY of LAST_VALUE without windowing clause, the default windowing clause will make LAST_VALUE to result unexpected as:

SQL> select
  2    empno, ename, deptno, sal,
  3    last_value(sal) over (partition by deptno order by sal) last_sal
  4  from scott.emp;

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

14 rows selected.

2 comments:

AnonymousMay 12, 2014 at 9:42 PM
Interesting....

I didnt realize that one had to use additional specifiers to make LAST_VALUE or FIRST_VALUE look across the partition

If it is a WINDOW function, it should, I would have thought, looked across the window (i.e.whole group of records within that window).

Thanks....
Eashwer Iyer
AnonymousMay 16, 2014 at 10:42 PM
Agreed. I never use LAST_VALUE because I didn't understand the need for the specific RANGE clause. Instead I always used FIRST_VALUE, then added DESC to the ORDER BY clause. I think that's worked very time for me, but I'm glad to finally understand why LAST_VALUE failed.

Thank you!