FIRST and LAST analytic functions in plsql

The FIRST function (or more properly KEEP FIRST function) is used in a very special situation. Suppose we rank a group of record and found several records in the first rank. Now we want to apply an aggregate function on the records of the first rank. KEEP FIRST enables that.

The general syntax is:

Function( ) KEEP (DENSE_RANK FIRST ORDER BY <expr>) OVER (<partitioning_clause>)

Please note that FIRST and LAST are the only functions that deviate from the general syntax of analytic functions. They do not have the ORDER BY inside the OVER clause. Neither do they support any <window> clause. The ranking done in FIRST and LAST is always DENSE_RANK. The query below shows the usage of FIRST function. The LAST function is used in similar context to perform computations on last ranked records.

-- How each employee's salary compare with the average salary of the first

-- year hires of their department?

SELECT empno, deptno, TO_CHAR(hiredate,'YYYY') HIRE_YR, sal,

TRUNC(

AVG(sal) KEEP (DENSE_RANK FIRST

ORDER BY TO_CHAR(hiredate,'YYYY') )

OVER (PARTITION BY deptno)

     ) AVG_SAL_YR1_HIRE

FROM emp

WHERE deptno IN (20, 10)

ORDER BY deptno, empno, HIRE_YR;

     EMPNO     DEPTNO HIRE        SAL AVG_SAL_YR1_HIRE

---------- ---------- ---- ---------- ----------------

      7782         10 1981       2450             3725

      7839         10 1981       5000             3725

      7934         10 1982       1300             3725

      7369         20 1980        800              800

      7566         20 1981       2975              800

      7788         20 1982       3000              800

      7876         20 1983       1100              800

      7902         20 1981       3000              800

8 rows selected.