Purpose
FIRST_VALUE() is an analytic function. It returns the first value in an ordered value list. 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 NULLs in the expr column is NULL.
Syntax
FIRST_VALUE { (expr) [{RESPECT | IGNORE} NULLS ]
| (expr [{RESPECT | IGNORE} NULLS ])
}
OVER (analytic_clause)
Parameters
| Parameter | Description |
|---|---|
| expr | The column name of the value to be returned. Notice: You cannot nest expr in analytic functions such as FIRST_VALUE(). |
| {RESPECT | IGNORE} NULLS | Optional. Specifies whether to ignore NULLs. 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, see Analytic functions. |
Return type
The return type is the same as the data type of expr, or this function returns NULL.
Examples
The table emp_msg has been created. 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