Oracle Database - SQL - First Value Analytic function

Card Puncher Data Processing

About

FIRST_VALUE is an non-deterministic analytic function.

It returns the first value in an ordered set of values.

IGNORE NULLS and Densification

If the first value in the set is null, then the function returns NULL unless you specify IGNORE NULLS.

This setting is useful for data densification. If you specify IGNORE NULLS, then FIRST_VALUE returns the fist non-null value in the set, or NULL if all values are null.

Examples

Non-deterministic

The following example selects, for each employee in Department 90, the name of the employee with the lowest salary.

SELECT department_id, last_name, salary, FIRST_VALUE(last_name)
  OVER (ORDER BY salary ASC ROWS UNBOUNDED PRECEDING) AS lowest_sal
  FROM (SELECT * FROM employees WHERE department_id = 90
    ORDER BY employee_id);
DEPARTMENT_ID LAST_NAME         SALARY LOWEST_SAL
------------- ------------- ---------- -------------------------
           90 Kochhar            17000 Kochhar
           90 De Haan            17000 Kochhar
           90 King               24000 Kochhar

The example illustrates the non-deterministic nature of the FIRST_VALUE function. Kochhar and DeHaan have the same salary, so are in adjacent rows. Kochhar appears first because the rows returned by the subquery are ordered by employee_id. However, if the rows returned by the subquery are ordered by employee_id in descending order, as in the next example, then the function returns a different value:

SELECT department_id, last_name, salary, FIRST_VALUE(last_name)
  OVER (ORDER BY salary ASC ROWS UNBOUNDED PRECEDING) as fv
    FROM (SELECT * FROM employees WHERE department_id = 90
      ORDER by employee_id DESC);
DEPARTMENT_ID LAST_NAME         SALARY FV
------------- ------------- ---------- -------------------------
           90 De Haan            17000 De Haan
           90 Kochhar            17000 De Haan
           90 King               24000 De Haan

Non-deterministic to deterministic

The following example shows how to make the FIRST_VALUE function deterministic by ordering on a unique key.

SELECT department_id, last_name, salary, hire_date, 
   FIRST_VALUE(last_name) OVER
   (ORDER BY salary ASC, hire_date ROWS UNBOUNDED PRECEDING) AS fv
   FROM (SELECT * FROM employees 
   WHERE department_id = 90 ORDER BY employee_id DESC);
DEPARTMENT_ID LAST_NAME         SALARY HIRE_DATE FV
------------- ------------- ---------- --------- ---------------
           90 Kochhar            17000 21-SEP-89 Kochhar
           90 De Haan            17000 13-JAN-93 Kochhar
           90 King               24000 17-JUN-87 Kochhar





Discover More
Card Puncher Data Processing
Oracle Database - Analytics

The Oracle Advanced Analytics option of Oracle Database 11g Enterprise Edition includes: Oracle Data Mining and a new component called Oracle R Enterprise, which embeds R analytic capability in the...
Card Puncher Data Processing
Oracle Database - SQL - Analytic Functions

Analytic functions in the Oracle Database Context An analytic function takes place after that the original data set is retrieved. The clause AVG CORR COVAR_POP COVAR_SAMP...



Share this page:
Follow us:
Task Runner