Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL FIRST_VALUE Function      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: FIRST_VALUE

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...
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 180 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?