Definition:
The Oracle PL/SQL FIRST_VALUE function is an analytic function which selects the first record from the partitioned and ordered set of rows. You can specify the IGNORE NULLS parameter to ignore the NULL values of the operand column and force consideration of NOT NULL values.
If the first value in the result set is NULL then the function returns NULL unless you specify IGNORE NULLS.
If you use the IGNORE NULLS parameter then FIRST_VALUE will return the first non-null value found in the result set. (If all values are null then it will return NULL.)
Example Syntax:
FIRST_VALUE(expression [INGORE NULLS]) OVER (analytic_clause)
Note that you cannot nest analytic functions by using FIRST_VALUE for expression.
Example Usage:
The SQL query below shows the 'Highest Salary' of employees who are reporting to the same manager.
SELECT EMPNO, ENAME, SAL,
FIRST_VALUE (SAL IGNORE NULLS) OVER (PARTITION BY MGRNO ORDER BY SAL DESC) "Highest Salary"
FROM EMPLOYEES
Related Links:
Related Code Snippets:
- FIRST_VALUE - Returns the first value in an ordered set of values. If the first value in the set i...
