Purpose
This function is an analytic function that returns the first value in an ordered set. 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 to return a value. Note You cannot nest an analytic function, such as FIRST_VALUE, in the expr column. |
| {RESPECT | IGNORE} NULLS | Specifies whether to ignore NULL values. This is an optional parameter. The default value is RESPECT NULLS.
|
| OVER | Use the OVER clause to define the window for calculation. For more information, see Analytic Function Description. |
Return type
The return value is the same as the data type of the expr parameter or NULL.
Examples
The emp_msg table has been created. Sort the data in the emp_msg table in descending order by the sal column, and fill the first_mgr column with the first non-null value in the 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