FIRST_VALUE

Syntax

For information on syntax and semantics, see "Analytic Functions".

Purpose

FIRST_VALUE is an analytic function. It returns the first value in an ordered set of values.

Examples

The following example selects, for each employee in Department 20, the name of the employee with the highest salary.

SELECT deptno, ename, sal, FIRST_VALUE(ename) 
   OVER (ORDER BY sal DESC ROWS UNBOUNDED PRECEDING) AS rich_emp
   FROM (SELECT * FROM emp WHERE deptno = 20 ORDER BY empno);

    DEPTNO ENAME             SAL RICH_EMP
---------- ---------- ---------- ----------
        20 SCOTT            3000 SCOTT
        20 FORD             3000 SCOTT
        20 JONES            2975 SCOTT
        20 ADAMS            1100 SCOTT
        20 SMITH             800 SCOTT

The example illustrates the nondeterministic nature of the FIRST_VALUE function. Scott and Ford have the same salary, so are in adjacent rows. Scott appears first because the rows returned by the subquery are ordered by EMPNO. However, if the rows returned by the subquery are ordered by EMPNO in descending order, as in the next example, the function returns a different value:

SELECT deptno, ename, sal, FIRST_VALUE(ename) 
   OVER (ORDER BY sal DESC ROWS UNBOUNDED PRECEDING) AS fv
   FROM (SELECT * FROM emp WHERE deptno = 20 ORDER BY empno desc);

    DEPTNO ENAME             SAL FV
---------- ---------- ---------- ----------
        20 FORD             3000 FORD
        20 SCOTT            3000 FORD
        20 JONES            2975 FORD
        20 ADAMS            1100 FORD
        20 SMITH             800 FORD

The following example shows how to make the FIRST_VALUE function deterministic by ordering on a unique key.

SELECT deptno, ename, sal, hiredate, FIRST_VALUE(ename) 
   OVER (ORDER BY sal DESC, hiredate ROWS UNBOUNDED PRECEDING) AS fv
   FROM (SELECT * FROM emp WHERE deptno = 20 ORDER BY empno desc);

DEPTNO     ENAME      SAL        HIREDATE  FV         
---------- ---------- ---------- --------- ----------
        20 FORD             3000 03-DEC-81 FORD      
        20 SCOTT            3000 19-APR-87 FORD      
        20 JONES            2975 02-APR-81 FORD      
        20 ADAMS            1100 23-MAY-87 FORD      
        20 SMITH             800 17-DEC-80 FORD