Purpose
You can call this function to return the first value in an ordered value list. It is an analytic function. If the first value in the set is NULL, the function returns NULL, unless you specify IGNORE NULLS, which is useful for data densification. The return value for rows with NULL values in the expr column is NULL.
Syntax
FIRST_VALUE { (expr) [{RESPECT IGNORE} NULLS ]
(expr [{RESPECT IGNORE} NULLS ])
}
OVER (analytic_clause)
Parameters
| Parameter | Description |
|---|---|
| expr | Specifies the column name of the value to be returned. Notice You cannot use analytic functions such as FIRST_VALUE as expr. |
| {RESPECT IGNORE} NULLS | (Optional) Specifies whether to ignore NULL values. Default value: RESPECT NULLS.
|
| OVER | You can use the OVER clause to define a window over the data on which the function operates. For more information about analytic functions, see Analytic functions. |
Return types
This function returns the same value type as the expr parameter or returns NULL.
Examples
Assume that you have created the table emp_msg. Sort data records in the emp_msg table by the sal column in descending order, and fill the first non-null value in the mgr column to the first_mgr column.
obclient> SELECT * FROM emp_msg;
+--------+--------+------+------+
DEPTNO ENAME SAL MGR
+--------+--------+------+------+
10 CLARK 2750 7839
10 KING 5300 NULL
10 MILLER 1600 7782
20 ADAMS 1400 7788
20 FORD 3300 7566
20 JONES 3275 7839
20 SCOTT 3300 7566
20 SMITH 1100 7902
30 ALLEN 1900 7698
30 BLAKE 3150 7839
30 JAMES 1250 7698
30 MARTIN 1550 7698
30 TURNER 1800 7698
30 WARD 1550 7698
30 SCLARK 1750 7839
+--------+--------+------+------+
15 rows in set
obclient> SELECT deptno,ename,sal,mgr,
FIRST_VALUE (MGR) IGNORE NULLS OVER (ORDER BY sal DESC) AS first_mgr
FROM emp_msg
ORDER BY sal DESC;
+--------+--------+------+------+-----------+
DEPTNO ENAME SAL MGR FIRST_MGR
+--------+--------+------+------+-----------+
10 KING 5300 NULL NULL
20 FORD 3300 7566 7566
20 SCOTT 3300 7566 7566
20 JONES 3275 7839 7566
30 BLAKE 3150 7839 7566
10 CLARK 2750 7839 7566
30 ALLEN 1900 7698 7566
30 TURNER 1800 7698 7566
30 SCLARK 1750 7839 7566
10 MILLER 1600 7782 7566
30 MARTIN 1550 7698 7566
30 WARD 1550 7698 7566
20 ADAMS 1400 7788 7566
30 JAMES 1250 7698 7566
20 SMITH 1100 7902 7566
+--------+--------+------+------+-----------+
15 rows in set (0.00 sec)