All Products
Search
Document Center

FIRST_VALUE

Last Updated: Jun 18, 2021

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      |
+--------+--------+------+------+-----------+