Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
Functions, 44 of 166
first_value::=
first_value
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.
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 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, 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
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|