Skip Headers

Oracle9i SQL Reference
Release 2 (9.2)

Part Number A96540-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page Go to next page
View PDF

FIRST_VALUE

Syntax

first_value::=

Text description of functions95.gif follows
Text description of first_value


See Also:

"Analytic Functions" for information on syntax, semantics, and restrictions

Purpose

FIRST_VALUE is an analytic function. It returns the first value in an ordered set of values.

You cannot use FIRST_VALUE or any other analytic function for expr. That is, you can use other built-in function expressions for expr, but you cannot nest analytic functions.

See Also:

"About SQL Expressions" for information on valid forms of expr

Examples

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

SELECT departmeent_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 nondeterministic 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

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