first_value {analytical function}
You cannot use FIRST_VALUE or any other analytic function for expr. That is, you cannot nest analytic functions, but you can use other built-in function expressions for expr. APPLIES TO: Oracle 9i, Oracle 10g, Oracle 11g SYNTAX: FIRST_VALUE(expr [IGNORE NULLS]) OVER (analytic clause) EXAMPLE: The following example selects, for each employee in Department 20 and 30, the name of the employee with the lowest salary. SELECT
OVERVIEW: Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a ‘window’ and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time. Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.
The Oracle SQL analytical function FIRST_VALUE returns the first value in an ordered set of values. 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.
emp.department_id,
emp.last_name,
emp.salary,
FIRST_VALUE(emp.last_name) OVER (PARTITION BY emp.department_id ORDER BY
salary ASC ) AS lowest_salary
FROM
employees emp
WHERE
emp.department_id in (20, 30);
department_id
last_name
salary
lowest_salary
20
Fay
6000
Fay
20
Hartstein
13000
Fay
30
Colmenares
2500
Colmenares
30
Himuro
2600
Colmenares
30
Tobias
2800
Colmenares
30
Baida
2900
Colmenares
30
Khoo
3100
Colmenares
30
Raphaely
11000
Colmenares