FIRST_VALUE is an analytic function. It returns the first value in a set of the ordered values. If the first value in the set is NULL, the function returns NULL unless you specify IGNORE NULLS. This configuration is useful for data densification.
Syntax
FIRST_VALUE { (expr) [ {RESPECT | IGNORE} NULLS ] | (expr [ {RESPECT | IGNORE} NULLS ]) } OVER (analytic_clause)
Parameters
| Parameter | Description |
|---|---|
| expr | The parameter type is not limited. |
| OVER | Uses the OVER clause to define a window for calculation. |
| {RESPECT | IGNORE} NULLS | Specifies whether to ignore NULL values. The default value is RESPECT NULLS and indicates that NULL values are taken into consideration. |
| FROM { FIRST | LAST } | Specifies whether the calculation starts from the first or last row of the window. The default value is FROM FIRST. If you specify IGNORE NULLS, FIRST_VALUE returns the first non-null value in the set. If all the values are NULL, NULL is returned. |
Return type
The data type is not limited.
Examples
To create the emp_msg table and insert data into the table, execute the following statements:
CREATE TABLE emp_msg(deptno INT, ename VARCHAR(30), sal INT, MGR VARCHAR(30));
INSERT INTO emp_msg VALUES(10,'CLARK', 2750, 7839);
INSERT INTO emp_msg VALUES(10,'KING', 5300, NULL);
INSERT INTO emp_msg VALUES(10,'MILLER', 1600, 7782);
INSERT INTO emp_msg VALUES(20,'ADAMS', 1400, 7788);
INSERT INTO emp_msg VALUES(20,'FORD', 3300, 7566);
INSERT INTO emp_msg VALUES(20,'JONES', 3275, 7839);
INSERT INTO emp_msg VALUES(20,'SCOTT', 3300, 7566);
INSERT INTO emp_msg VALUES(20,'SMITH', 1100, 7902);
INSERT INTO emp_msg VALUES(30,'ALLEN', 1900, 7698);
INSERT INTO emp_msg VALUES(30,'BLAKE', 3150, 7839);
INSERT INTO emp_msg VALUES(30,'JAMES', 1250, 7698);
INSERT INTO emp_msg VALUES(30,'MARTIN', 1550, 7698);
INSERT INTO emp_msg VALUES(30,'TURNER', 1800, 7698);
INSERT INTO emp_msg VALUES(30,'WARD', 1550, 7698);
Query the highest and the first non-null MGR value in the sal column in the emp_msg table and use the queried value as the first_MGR column.
SELECT deptno , ename , sal , MGR ,
FIRST_VALUE ( MGR ) IGNORE NULLS over ( ORDER BY sal DESC ROWS UNBOUNDED PRECEDING ) AS first_MGR
FROM emp_msg ORDER BY deptno , ename;
The following query result is returned:
+--------+--------+------+------+-----------+
| DEPTNO | ENAME | SAL | MGR | FIRST_MGR |
+--------+--------+------+------+-----------+
| 10 | CLARK | 2750 | 7839 | 7566 |
| 10 | KING | 5300 | NULL | NULL |
| 10 | MILLER | 1600 | 7782 | 7566 |
| 20 | ADAMS | 1400 | 7788 | 7566 |
| 20 | FORD | 3300 | 7566 | 7566 |
| 20 | JONES | 3275 | 7839 | 7566 |
| 20 | SCOTT | 3300 | 7566 | 7566 |
| 20 | SMITH | 1100 | 7902 | 7566 |
| 30 | ALLEN | 1900 | 7698 | 7566 |
| 30 | BLAKE | 3150 | 7839 | 7566 |
| 30 | JAMES | 1250 | 7698 | 7566 |
| 30 | MARTIN | 1550 | 7698 | 7566 |
| 30 | TURNER | 1800 | 7698 | 7566 |
| 30 | WARD | 1550 | 7698 | 7566 |
+--------+--------+------+------+-----------+