|
|
|
FIRST_VALUE windowing function
Syntax
FIRST_VALUE ( [scalar_expression ] ) OVER ( [ partition_by_clause ] order_by_clause )
Description
The analytic function FIRST_VALUE returns the first value of an ordered set of values. The argument column_ref or expression can be of any type. It must result in a single value. The result of the function is of the same data type as that of the argument. The result can have a null value.
Example
select empno, deptno, projno, sal, first_value(sal) over (partition by deptno, projno order by sal) lowest_sal from emp;
EMPNO DEPTNO PROJNO SAL LOWEST_SAL
----- ------ ------ --- -----------
7782 10 101 2450.00 2450.00
7934 10 102 1300.00 1300.00
7839 10 102 5000.00 1300.00
7329 20 101 800.00 800.00
7876 20 101 1100.00 800.00
7566 20 101 2975.00 800.00
7788 20 101 3000.00 800.00
7902 20 101 3000.00 800.00
7698 30 101 2850.00 2850.00
7900 30 102 950.00 950.00
7844 30 102 1500.00 950.00
7521 30 103 1250.00 1250.00
7654 30 103 1250.00 1250.00
7499 30 103 1600.00 1250.00
Note
Analytic functions are recalculated for each new value in the ordered result set. Therefore, first_value() will return the same value for each row in a partition. The return value for last_value() within a partition will change as each new value in the order_by_clause is returned. The same value will be returned for each row in the result set with the same value from the order_by_clause.
See Also
