Databases Reference
In-Depth Information
When we look at
EMPNO 7521
(
WARD
) again, we see that the
DEPARTMENT_TOTAL
column shows
2850
. This is a summary of all preceding salaries within his
department (1600 + 1250). Just to show the effect of a running total more clearly,
I adjusted the inal
order by
predicate to match the window's sort condition. It
should be noted that there's no need to do this. When the inal
order by
is different
from the sort order in the partition, the inal result can be rather confusing. The inal
order by
has no impact on the values determined by the analytic functions.
Visualizing the window
To help you visualize where a window starts and where it ends, you can use the
analytic functions
FIRST_VALUE
and
LAST_VALUE
, which return the speciied
column value for the irst or last record in the window:
SQL> select ename
2 , sum (sal) over (partition by deptno
3 order by empno
4 ) dept_total
5 , first_value (ename) over (partition by deptno
6 order by empno
7 ) fv
8 , last_value (ename) over (partition by deptno
9 order by empno
10 ) lv
11 from emp
12 where deptno = 20
13 order by deptno
14 , empno
15 /
ENAME DEPT_TOTAL FV LV
---------- ---------- ---------- ----------
SMITH 800 SMITH SMITH
JONES 3775 SMITH JONES
SCOTT 6775 SMITH SCOTT
ADAMS 7875 SMITH ADAMS
FORD 10875 SMITH FORD
Here you can see that the window is expanding per row. When we look at
SCOTT
, the window starts with the employee named
SMITH
and ends with the
current row (
SCOTT
).
