Analytics Using KEEP.

If we need to group records by certain fields and founded that some records Occupy Top most Position.
And If you need to apply Aggregates only those Top most record, this can be Easily done by using Analytics.

"Analytics are the coolest thing to happen to SQL since the keyword Select" & Comparitive Window functions are
going to be Even more Collest.

scott@10G> SELECT empno,
  2    ename,
  3    sal,
  4    deptno,
  5    TO_CHAR(hiredate,'yyyy')                                                  AS hiredate,
  6    dense_rank () over(partition BY deptno order by TO_CHAR(hiredate,'yyyy')) AS dnk
  7  FROM emp
  8  ORDER BY 4,5
  9  /

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

14 rows selected.



Now, how to apply Aggregates only to those records with Dnk = 1 ?


scott@10G> SELECT empno,
  2    ename,
  3    sal,
  4    deptno,
  5    TO_CHAR(hiredate,'yyyy')                                                  AS hiredate,
  6    Avg(sal) keep(dense_rank first order by TO_CHAR(hiredate,'yyyy')) over(partition BY deptno) AS dnk
  7  FROM emp
  8  ORDER BY 4,5
  9  /

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

14 rows selected.

Elapsed: 00:00:00.04