Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

FIRST_VALUE and LAST_VALUE Analytic Functions

This article gives an overview of the FIRST_VALUE and LAST_VALUE analytic functions.

Related articles.

FIRST_VALUE

The FIRST_VALUE analytic function is similar to the FIRST analytic function, allowing you to return the first result from an ordered set.

SELECT empno,
       deptno,
       sal,
       FIRST_VALUE(sal) IGNORE NULLS 
         OVER (PARTITION BY deptno ORDER BY sal) AS lowest_in_dept
FROM   emp;

     EMPNO     DEPTNO        SAL LOWEST_IN_DEPT
---------- ---------- ---------- --------------
      7934         10       1300           1300
      7782         10       2450           1300
      7839         10       5000           1300
      7369         20        800            800
      7876         20       1100            800
      7566         20       2975            800
      7788         20       3000            800
      7902         20       3000            800
      7900         30        950            950
      7654         30       1250            950
      7521         30       1250            950
      7844         30       1500            950
      7499         30       1600            950
      7698         30       2850            950

SQL>

The "{RESPECT | IGNORE} NULLS" clause indicates if NULLs are considered when determining results.

The windowing clause can be used to alter the window of operation. The following example uses "ROWS 1 PRECEDING" to give a result similar, but not quite the same, to a LAG of 1 row.

SELECT empno,
       deptno,
       sal,
       FIRST_VALUE(sal) IGNORE NULLS
         OVER (PARTITION BY deptno ORDER BY sal ROWS 1 PRECEDING) AS preceding_in_dept
FROM   emp;

     EMPNO     DEPTNO        SAL PRECEDING_IN_DEPT
---------- ---------- ---------- -----------------
      7934         10       1300              1300
      7782         10       2450              1300
      7839         10       5000              2450
      7369         20        800               800
      7876         20       1100               800
      7566         20       2975              1100
      7788         20       3000              2975
      7902         20       3000              3000
      7900         30        950               950
      7654         30       1250               950
      7521         30       1250              1250
      7844         30       1500              1250
      7499         30       1600              1500
      7698         30       2850              1600

SQL>

LAST_VALUE

The LAST_VALUE analytic function is similar to the LAST analytic function, allowing you to return the last result from an ordered set. Using the default windowing clause the result can be a little unexpected.

SELECT empno,
       deptno,
       sal,
       LAST_VALUE(sal) IGNORE NULLS
         OVER (PARTITION BY deptno ORDER BY sal) AS highest_in_dept
FROM   emp;

     EMPNO     DEPTNO        SAL HIGHEST_IN_DEPT
---------- ---------- ---------- ---------------
      7934         10       1300            1300
      7782         10       2450            2450
      7839         10       5000            5000
      7369         20        800             800
      7876         20       1100            1100
      7566         20       2975            2975
      7788         20       3000            3000
      7902         20       3000            3000
      7900         30        950             950
      7654         30       1250            1250
      7521         30       1250            1250
      7844         30       1500            1500
      7499         30       1600            1600
      7698         30       2850            2850

SQL>

This is because the default windowing clause is "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW", which in this example means the current row will always be the last value. Altering the windowing clause to "RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" gives us the result we probably expected.

SELECT empno,
       deptno,
       sal,
       LAST_VALUE(sal) IGNORE NULLS
         OVER (PARTITION BY deptno ORDER BY sal RANGE BETWEEN
           UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS highest_in_dept
FROM   emp;

     EMPNO     DEPTNO        SAL HIGHEST_IN_DEPT
---------- ---------- ---------- ---------------
      7934         10       1300            5000
      7782         10       2450            5000
      7839         10       5000            5000
      7369         20        800            3000
      7876         20       1100            3000
      7566         20       2975            3000
      7788         20       3000            3000
      7902         20       3000            3000
      7900         30        950            2850
      7654         30       1250            2850
      7521         30       1250            2850
      7844         30       1500            2850
      7499         30       1600            2850
      7698         30       2850            2850

SQL>

As with the previous function, the "{RESPECT | IGNORE} NULLS" clause indicates if NULLs are considered when determining results. The default action is RESPECT NULLS.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.